excel - VBA Runtime Error 1004 "Application-defined or Object-defined error" when Selecting Range -

i having issue error 1004 "application-defined or object-defined error" when selecting range.

i still able select rows (ie rows("21:21").select) , select ranges in other sheets of same workbook. not believe error in code. maybe setting unaware of?

i have used exact same code many times before reason cannot make function in sub (i have commented error occurs)...

sub copysheet1_to_pastesheet2()      dim clastfundrow integer     dim cfirstblankrow integer      'finds last row of content     windows("excel.xlsm").activate     sheets("sheet1").activate     range("c21").select          '>>>error 1004 "application-defined or object-defined error" occurs     selection.end(xldown).select     clastfundrow = activecell.row     'finds first row without content     cfirstblankrow = clastfundrow + 1      'copy data     range("a21:c" & clastfundrow).select     selection.copy     'paste data values     sheets("paltrakexport portfolioaidname").select     range("a21").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      'bring top of sheet consistancy     range("a21").select     range("a1").select end sub 

i need fancy in copying amount of rows change frequently. again, below code has been used before without error... not in instance.

dim clastfundrow integer dim cfirstblankrow integer  'finds last row of content windows("excel.xlsm").activate sheets("sheet1").activate range("c21").select      '>>>error 1004 "application-defined or object-defined error" occurs selection.end(xldown).select clastfundrow = activecell.row 'finds first row without content cfirstblankrow = clastfundrow + 1 

perhaps code behind sheet1, when change focus sheet2 objects cannot found? if that's case, specifying target worksheet might help:

sheets("sheet1").range("c21").select 

i'm not familiar how select works because try avoid as possible :-). can define , manipulate ranges without selecting them. it's idea explicit reference. way, don't lose track if go 1 sheet or workbook another. try this:

option explicit  sub copysheet1_to_pastesheet2()      dim clastfundrow integer     dim cfirstblankrow integer     dim wkssource worksheet, wksdest worksheet     dim rngstart range, rngsource range, rngdest range      set wkssource = activeworkbook.sheets("sheet1")     set wksdest = activeworkbook.sheets("sheet2")      'finds last row of content     clastfundrow = wkssource.range("c21").end(xldown).row     'finds first row without content     cfirstblankrow = clastfundrow + 1      'copy data     set rngsource = wkssource.range("a2:c" & clastfundrow)      'paste data values     set rngdest = wksdest.range("a21")     rngsource.copy     rngdest.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false     'bring top of sheet consistancy     wksdest.range("a1").select  end sub