Thread: Macro to copy column to new sheet

Okay VBA People. See attached. I need a macro to copy column B of sheet 1 to Column A of sheet 2 . BUT WAIT, THERE'S MORE!! I will be updating the numbers of column B in Sheet 1 constantly. I would like to make sheet 2 an archive sheet for all the numbers that have been in column B of sheet 1. So what I need the macro to do is copy Column B of sheet 1 to Column A of Sheet 2, then the next time I run it, it needs to copy Column B of sheet 1 to Column B of sheet 2. Understand? Basically, everytime I run the macro it needs to copy Column B to the next empty column of Sheet 2.

THANKS IN ADVANCE!!!!!!!

Option Explicit

Sub ArchiveColumnB()

Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
Dim ws2NextColumn As Long

With ws2
    If WorksheetFunction.CountA(.Columns(1)) = 0 Then
        ws2NextColumn = 1
    Else
        ws2NextColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
    End If
End With
With ws1
    .Columns(2).Copy ws2.Cells(1, ws2NextColumn)
End With
End Sub

Regards

You're welcome.