excel - Method Range of object_worksheet failed 1004 -

i wrote code works should when debug it. when remove breakpoint , run code, give runtime error:

runtime error '1004' method range of object_worksheet failed.

it refers next line:

set copyrange = sh.range("a" & & ":e" & & ",i" & & ":o" & & ",q" & & ",v" & i) 'name column in sheet  = q 

but while debugging it, there isn't problem. maybe cache full?

private sub btngetdevices_click() 'open every sheet after summary 'copy columns a,b,c,d,e,i,j,k,l,m,n,o, q,v summary  dim sh worksheet dim copyrange range  application.screenupdating = false sheets("summary").rows(4 & ":" & sheets("summary").rows.count).delete each sh in activeworkbook.worksheets     if sh.name <> "database" , sh.name <> "template" , sh.name <> "help" , sh.name <> "overview" , sh.name <> "develop" , sh.name <> "schedule" , sh.name <> "information" , sh.name <> "announcements" , sh.name <> "summary"         sh.select         lastrow = activesheet.range("l1048555").end(xlup).row          = 14 lastrow                if sh.range("q" & i).value <> empty , sh.range("n" & i).value <> "designer" , sh.range("o" & i).value <> "layouter"               set copyrange = sh.range("a" & & ":e" & & ",i" & & ":o" & & ",q" & & ",v" & i) 'name column in sheet  = q               namedevice = sh.range("q" & i).value               adressdevice = sh.range("q" & i)               copyrange.copy               sheets("summary").select                lastrowsummary = activesheet.range("a1048555").end(xlup).row               range("b" & lastrowsummary + 1).select               activesheet.paste               range("a" & lastrowsummary + 1) = sh.name               range("a" & lastrowsummary + 1, "o" & lastrowsummary + 1).borders.linestyle = xlcontinuous                sheets("summary").hyperlinks.add anchor:=sheets("summary").range("n" & lastrowsummary + 1), address:="", subaddress:="'" & sh.name & "'!a1", texttodisplay:=namedevice               end if            next       end if   next application.screenupdating = true  sheets("summary").activate  end sub 

*edit: after testing noticed error gone when use full range of columns instead of columns.

with error:

set copyrange = sh.range("a" & & ",v" & i) 

w/o error:

set copyrange = sh.range("a" & & ":e" & i) 

*second edit:

i'm using code 'tim williams'. there same error. instead of using:

rw.range("a1:e1,i1:o1,q1,v1").copy rng.offset(0, 1) 

i've found workaround. split up.

rw.range("i1:o1").copy rng.offset(0, 6) rw.range("q1").copy rng.offset(0, 13) rw.range("v1").copy rng.offset(0, 14) 

now works without error. if knows causes problem, may share it. in advance.

*third edit:

i still don't know why doesn't work. has range different columns. funny (and frustrated part) use range way in sheet , there don't have problem. driving me mad. have idea?

compiled not tested"

private sub btngetdevices_click() 'open every sheet after summary 'copy columns a,b,c,d,e,i,j,k,l,m,n,o, q,v summary  dim sh worksheet, shtsumm worksheet dim copyrange range, arrexclude, rw range dim lastrow long, long, rng range dim namedevice, adressdevice      'sheets ignore     arrexclude = array("database", "template", "help", "overview", _                       "develop", "schedule", "information", "announcements", _                       "summary")      set shtsumm = sheets("summary")      application.screenupdating = false      shtsumm.rows(4 & ":" & shtsumm.rows.count).delete     each sh in activeworkbook.worksheets          if iserror(application.match(sh.name, arrexclude, 0))              lastrow = sh.cells(sh.rows.count, "l").end(xlup).row              = 14 lastrow                    set rw = sh.rows(i)                    if rw.cells(1, "q").value <> empty , _                      rw.cells(1, "n").value <> "designer" , _                      rw.cells(1, "o").value <> "layouter"                        namedevice = rw.range("q1").value                       adressdevice = rw.range("q1").value '<<<typo ?                        'find destination                       set rng = shtsumm.cells(rows.count, "a").end(xlup).offset(1, 0)                        rng.value = sh.name                       'here range relative *rw*, not whole sheet                       rw.range("a1:e1,i1:o1,q1,v1").copy rng.offset(0, 1)                       rng.resize(1, 15).borders.linestyle = xlcontinuous                        shtsumm.hyperlinks.add _                          anchor:=rng.entirerow.cells(1, "n"), _                          address:="", subaddress:="'" & sh.name & "'!a1", _                          texttodisplay:=namedevice                   end if             next         end if     next      application.screenupdating = true      shtsumm.activate  end sub