vba - Excel 2010 - Select method of range class failed -

i want run following vba code:

sub combinesheets()  dim rngpaste range 'range paste dim rngcopy range 'range copy dim wb excel.workbook   dim strrange string 'range in sheets copy strrange = "a2:a10"  set rngpaste = activeworkbook.worksheets("combined").range(strrange) 'initial range paste  set wb = activeworkbook dim s integer s = 2 sheets.count   'copy down value     wb.worksheets(s).range("a:a").select ' error: here it!     wb.worksheets(s).range("a7").activate     selection.insert shift:=xltoright, copyorigin:=xlformatfromleftorabove     wb.worksheets(s).rows("4:4").select     selection         .horizontalalignment = xlgeneral         .wraptext = false         .orientation = 0         .addindent = false         .indentlevel = 0         .shrinktofit = false         .readingorder = xlcontext         .mergecells = true     end     selection.unmerge     wb.worksheets(s).range("b4").select     selection.copy     wb.worksheets(s).range("a5").select     activesheet.paste     wb.worksheets(s).range("a6").select     activesheet.paste     wb.worksheets(s).range("a7").select     activesheet.paste     wb.worksheets(s).range("a8").select     activesheet.paste     wb.worksheets(s).range("a9").select     activesheet.paste     wb.worksheets(s).range("a10").select     activesheet.paste     'delete first 4 rows     wb.worksheets(s).rows("1:4").select     selection.delete shift:=xlup     '-------------------      'copy new sheet     set rngcopy = activeworkbook.worksheets(s).range(strrange) 'copy same range in each sheet      rngpaste.value = rngcopy.value 'copy values first sheet      set rngpaste = rngpaste.offset(10, 0) 'moves paste range next copy  next s  end sub 

however, error: run-time error 1004: select method of range class failed.

any suggestion doing wrong?

i appreciate reply!

you cannot select cell or range in worksheet not in foreground. instead must bring sheet foreground activating beforehand. should add

activeworkbook.worksheets(s).activate 

as first line in for loop. this:

for s = 2 sheets.count      activeworkbook.worksheets(s).activate  ' <--- add line here   'copy down value     wb.worksheets(s).range("a:a").select ' error: here it!     wb.worksheets(s).range("a7").activate     selection.insert shift:=xltoright, copyorigin:=xlformatfromleftorabove     wb.worksheets(s).rows("4:4").select  ' [...]