VBA: loop to copy certain columns from main sheet, create a new sheet, and paste

Question

I have a sheet with a specific number columns. I need to copy columns A and another column starting with column B, create a new sheet and paste those columns there. I then want to loop it so that it copies columns A and C this time, then create a new sheet and paste, and so forth until it reaches the last column on the main sheet. Column A is fixed so that it is always copied, the second column copied is the one that varies. I'm thinking something like this inside the loop

Sheets(1).Activate
Range("A1:A14").Select
'This is where I need to copy the next column over and increment every time the code loops
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste

Help would be appreciated. Thank You.

stobin stobin · Accepted Answer · 2014-08-07T22:19:18

Your best bet is to use Cells() inside of your Range() in order to loop through each column. This bit of code should help you out:

Sub columnCopy()

Dim sh1 As Worksheet
Set sh1 = sheets("Sheet1")

lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column ' Last Column

For i = 2 To lc

    sh1.Range(sh1.Cells(1, 1), sh1.Cells(12, 1)).Copy
    Worksheets.Add
    sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues

    sh1.Range(sh1.Cells(1, i), sh1.Cells(12, i)).Copy
    sheets(1).Cells(1, 2).PasteSpecial Paste:=xlPasteValues

Next i

End Sub

VBA: loop to copy certain columns from main sheet, create a new sheet, and paste

1 Answers