runtime error - Excel VBA select method of range class failed -

runtime error - Excel VBA select method of range class failed -

i trying re-create ranges of info various worksheets 1 worksheet. have written code:

sub sub1() = 1 3 sheets(i).select range(cells(1, 1), cells(1, 1).end(xldown)).select 'line4 selection.copy sheets(6).select cells(1, i).select selection.pastespecial xlpastevalues next end sub

i run-time error '1004' select method of range class failed on line 4. how can fixed?

you don't select sheet activate it. shouldn't either in cases.

you can shorten code to:

sub sub1() dim long = 1 3 sheets(i) .range(.cells(1, 1), .cells(1, 1).end(xldown)).copy end sheets(6).cells(1, i).pastespecial xlpastevalues next end sub

note declared i. recommend declaring variables, , using option explicit create sure you're using variable think in cases.

edit: simoco's edit good: here's came with:

sub sub1() dim long dim wb excel.workbook set wb = activeworkbook = 1 3 wb.sheets(i) .range("a1:a" & .range("a1").end(xldown).row).copy wb.sheets(6).cells(1, i).pastespecial xlpastevalues end next end sub

note declared workbook variable , qualified it. 1 more practice you!

excel-vba runtime-error