Copying data to a blank column

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

I would like to copy data from 1 column in sheet1 starting at row 12 to the
first empty column starting at row 8 in sheet2.
 
sub trythis()'SAS untested
sc=2
slr=sheets("sheet1").cells(rows.count,sc).end(xlup).row
with sheets("sheet2")
dlc=.cells(1,columns.count).end(xltoleft).column+1
sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc)
end with
end sub
 
Oops. I forgot to add a crucial dot before COPY
sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc)
Click to expand...
sheets("sheet1").cells(12,sc).resize(slr).copy .cells(8,dlc)
 
Hi,

The question is not clear - looking for the first blank column starting in
what column?

Assuming you mean starting in Column A of Sheet2 then this one line will do
it while you are on sheet1.

Range("A12:A" & [A12].End(xlDown).Row).Copy
Sheet2.[A8].End(xlToRight).Offset(0, 1)
 
This did nothing at all.


Shane Devenshire said:
Hi,

The question is not clear - looking for the first blank column starting in
what column?

Assuming you mean starting in Column A of Sheet2 then this one line will
do
it while you are on sheet1.

Range("A12:A" & [A12].End(xlDown).Row).Copy
Sheet2.[A8].End(xlToRight).Offset(0, 1)


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Ed Davis said:
I would like to copy data from 1 column in sheet1 starting at row 12 to
the
first empty column starting at row 8 in sheet2.
Click to expand...
Click to expand...
 
This code stops at the same column every time. It will overwrite whatever
is there. I need it to go to the next free column.
 
I do not want to tread on Don's toes but I think he is in a distant time
zone

The line
dlc=.cells(1,columns.count).end(xltoleft).column+1
looks at row 1 and finds the last item in it and then adds 1
The paste happens in the column set by DLC
You might want to try
dlc=.cells(8,columns.count).end(xltoleft).column+1
to have it look at row 8 when finding where to paste

best wishes
 
Final solution which allows for changing rows/columns.File returned to OP

Sub CopySheet1ColGtoSheet2()
Application.ScreenUpdating = False
sc = 7 'col G
Sfr = 11 'row 11 on source sheet
slr = Sheets("sheet1").Cells(Rows.Count, sc).End(xlUp).Row
dr = 8 'row 8 on destination sheeet

With Sheets("sheet2")
dlc = .Cells(dr, Columns.Count).End(xlToLeft).Column + 1
Sheets("sheet1").Cells(Sfr, sc).Resize(slr - Sfr + 1).Copy
..Cells(dr, dlc).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
..Cells(dr, dlc).PasteSpecial Paste:=xlPasteFormats
Range("b6").Copy .Cells(7, dlc)
..Columns(dlc).AutoFit
End With

'calls macro below automatically
CopySheet1andRename

Application.ScreenUpdating = True
End Sub
 
Back
Top