excel - VBA Cannot Refer To Range In Sheet That Exists Prior To Code -

i building code creates bunch of formatted data in 1 worksheet, active worksheet, transfers data new worksheet.

the problem when try specify sheets name, doesn't work if sheet existed prior code running.

so, in code below, error "'range' of obect '_worksheet' failed" @ line ".range(cells(1,1), cells(7,3)).interior.color = rgb(100,100,100)" if "topline" worksheet existed before running code. if delete worksheet , let code add , name "topline" worksheet, same error @ line "data.range(cells(x + 2, 1), cells(x + a, 3)).copy"

i've tried googling around @ loss why code works on worksheets didn't exist before running it. appreciated. i'm willing bet answer obvious.

here's code that's troubling me:

for = 1 worksheets.count     if worksheets(i).name = "topline" te = true     if worksheets(i).name = "data" de = true next  if te = false worksheets.add(after:=worksheets(1)).name = "topline" if de = false activesheet.name = "data"  = 1 worksheets.count     if worksheets(i).name = "topline" set topline = worksheets(i)     if worksheets(i).name = "data" set data = worksheets(i) next  topline     .range(cells(1, 1), cells(7, 3)).interior.color = rgb(100, 100, 100)     .range(cells(3, 1), cells(3, 3))         .merge         .font.bold = true         .value = "total"     end     .range(cells(4, 2), cells(5, 3))         .font.bold = true     end     .cells(1, 1) = "topline"     .cells(1, 1).font.bold = true     .cells(4, 2) = "count"     .cells(4, 3) = "percent" end  data.range(cells(x + 2, 1), cells(x + a, 3)).copy topline.range(cells(8, 1), cells(a + 6, 3)).pastespecial (xlpasteall) 

the problem have unqualified range references. when refer range object without first specifying worksheet, it's unqualified. references follow rules behind scenes , use objects underscores in them. might errors referring _worksheets or _global.

an unqualified reference in sheet's class module refer sheet. other unqualified references (thisworkbook, standard module, userform) refer activesheet. this

with topline     .range(cells(1, 1), cells(7, 3)).interior.color = rgb(100, 100, 100) end 

is saying

topline.range(activesheet.cells(1, 1), activesheet.cells(7, 3)).interior.color = rgb(100, 100, 100) 

since there not range on topline includes cells activesheet, error.

why happen when sheet exists? because while troubleshooting code went ui, created sheet, code run it. left newly created topline sheet active , when topline , activesheet same thing, no error.