need macro to copy and paste a column in one worksheet into the first blank column in another

[bcselect]

need macro to copy and paste a column from one worksheet into the first blank column in another

 

[JoeMo]

Sub bcselect()
'assume paste col C from sheet1 to first blank col in sheet2
Dim lCol As Long
lCol = Sheets("Sheet2").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
With Sheets("sheet1")
    .Range("C:C").Copy Destination:=Sheets("Sheet2").Columns(lCol + 1)
End With
End Sub

 

[bcselect]

Works great! Thanks

 

[bcselect]

spoke to soon. not working. doesn't transfer column to 2nd sheet

 

[JoeMo]

spoke to soon. not working. doesn't transfer column to 2nd sheet

What happens when you run the code? Is there a runtime error? If so, what's the error message and what line is highlighted? Are you using the correct name for the second sheet in the code? Is the second sheet blank prior to running the code? Is the column to be transferred empty?

 

[johnnyL]

@JoeMo my testing of your code, results = your code copies the column to the end of the used column range, not the first blank column encountered.

 

[JoeMo]

@JoeMo my testing of your code, results = your code copies the column to the end of the used column range, not the first blank column encountered.

This revision will paste to first blank column in Sheet2 used range if there is one, and to the first column to the right of the used range if there is not a blank column within the used range of Sheet2.

Sub bcselect()
'assume paste col C from sheet1 to first blank col in sheet2
Dim col As Range, lCol As Long
For Each col In Sheets("Sheet2").UsedRange.Columns
    If Application.CountA(col) = 0 Then
        With Sheets("sheet1")
            .Range("C:C").Copy Destination:=col
            GoTo Nx
        End With
    End If
Next col
lCol = Sheets("Sheet2").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
With Sheets("sheet1")
    .Range("C:C").Copy Destination:=Sheets("Sheet2").Columns(lCol + 1)
End With
Nx:
End Sub

 

[johnnyL]

This revision will paste to first blank column in Sheet2 used range if there is one, and to the first column to the right of the used range if there is not a blank column within the used range of Sheet2.

@JoeMo No need to vaguely word it, just say 'whoops, here you go, now it copies the column to the first blank column found on the other sheet" !!!

The way you stated it, it sounds like it could copy the column twice to the other sheet. And vs Or basically.

 

[bcselect]

Works fine. Thanks!!!

 

[JoeMo]

Works fine. Thanks!!!

You are welcome - thanks for the reply.