macro to copy data and paste to a new sheet in next available cell

[Neil]

Hi everyone

just looking for some guidance here.

i know how to record the macro to copy and paste the data, but i'm
wanting the macro to paste the data into the next available free cell
starting in a different column.

i.e.
range of data to copy- a1:a5
new sheet paste location - h10:L10

any help would be greatly appreciated.

Thanks

 

[Dave Peterson]

Same sheet or different sheets???

Dim FromRng as range
dim ToCell as range

set fromrng = worksheets("Sheet1").range("a1:a5")
set tocell = worksheets("Sheet2").range("H10") 'could even be the same sheet

fromrng.copy
tocell.pastespecial transpose:=true

===============

Or the next available cell in column H:

Dim FromRng as range
dim ToCell as range

set fromrng = worksheets("Sheet1").range("a1:a5")

with worksheets("Sheet2")
set tocell = .cells(.rows.count,"H").end(xlup).offset(1,0)
end with

fromrng.copy
tocell.pastespecial transpose:=true

===========
And if the ranges were on the same sheet:

with worksheets("Sheet9999")
set fromrng = .range("a1:a5")
set tocell = .cells(.rows.count,"H").end(xlup).offset(1,0)
end with

 

[Neil]

Thanks Dave. It works perfectly

Cheers
Neil

- Hide quoted text -