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

  • Thread starter Thread starter Neil
  • Start date Start date
N

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
 
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
 
Back
Top