excel - Exxel Macro Copy Selection area and Paste -

excel - Exxel Macro Copy Selection area and Paste -

this have.

i trying excel re-create cells have selected , paste on next blank line in spreadsheet.

but in code below, fixed range of cell beingness copied. how should alter code can me dynamic range?

sub copypaste() range("a6:e6").select selection.copy sheets("sheet2").select lmaxrows = cells(rows.count, "a").end(xlup).row range("a" & lmaxrows + 1).select selection.pastespecial paste:=xlvalues, operation:=xlnone, skipblanks:= _ false, transpose:=false lmaxrows = cells(rows.count, "a").end(xlup).row range("a" & lmaxrows + 1).select end sub

remove statement

range("a6:e6").select

this statement selects fixed range.

try this

sub copypaste() dim sht worksheet dim rngtarget range dim lmaxrows long selection.copy set sht = sheets("sheet2") lmaxrows = sht.cells(rows.count, "a").end(xlup).row set rngtarget = sht.range("a" & lmaxrows + 1) rngtarget.pastespecial paste:=xlvalues, operation:=xlnone, skipblanks:=false, transpose:=false lmaxrows = sht.cells(rows.count, "a").end(xlup).row sht.activate sht.range("a" & lmaxrows + 1).select end sub

i have rewritten code specify cells , ranges used. if not, apply selections on sheet open (active) @ moment.

in experience, using .select error-prone seek avoid much possible.

excel vba