Selecting different columns with VBA

Question

I have a worksheet with 22 columns of data and I need to copy and paste the first column and individual columns starting at column 3 to new worksheets in the same workbook. For example, I would select column A and column C and copy those two columns to a new worksheet. Then, I would go back to the initial worksheet and select column A and column D and copy just those two columns to a new worksheet, etc.

The code below iterates over all of the columns but it always selects a continuous range instead of just column A and the next column in the series. My initial code is below:

Sub CashFlowColumnTabSplit()

ActiveSheet.Name = "Main"

For i = 3 To 22
    Range(Columns(1), Columns(i)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Main").Select
Next i

End Sub

RADO RADO · Accepted Answer · 2017-06-07T22:59:38

Try:

Sub CashFlowColumnTabSplit()
Dim Source_Sheet As Worksheet
Dim Target_Sheet As Worksheet
Dim i As Long

    Set Source_Sheet = ThisWorkbook.Sheets("Main")

    For i = 3 To 22
        Set Target_Sheet = ThisWorkbook.Sheets.Add(After:=Source_Sheet)
        Application.Union(Source_Sheet.Columns(1), Source_Sheet.Columns(i)).Copy
        Target_Sheet.Range("A1").PasteSpecial xlPasteAll
    Next i

End Sub

Selecting different columns with VBA

2 Answers