Copying data to a blank column

[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.

 

[Don Guillett]

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

 

[Ed Davis]

I get compile errors no matter what I do with this code.

 

[Don Guillett]

Oops. I forgot to add a crucial dot before COPY

sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc)

sheets("sheet1").cells(12,sc).resize(slr).copy .cells(8,dlc)

 

[Shane Devenshire]

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)

 

[Ed Davis]

This did nothing at all.

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

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.

 

[Ed Davis]

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.

 

[Bernard Liengme]

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

 

[Don Guillett]

You may, very well, be correct. I assumed that OP had headers to the last
column.

 

[Don Guillett]

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

 

[Don Guillett]

email bounced due to "box full"..