Changing the columns to copy

[Guest]

I am trying to write a macro that will copy 3 columns into a new sheet. Once
this is done and I have run the other part of my macro I want it to go back
and copy another 3 columns. I always want to copy column "A" each time. The
1st time I want "A,B,C" then columns "A,D,E". "A,F,G" etc.

Currently I have:

Sheets("SBS Graphs - Main").Select
Range("A:A, B:B, C:C").Select
Range("C1").Activate
Selection.Copy
Sheets("Test").Select
Range("A1").Select
ActiveSheet.Paste

<remaining macro>

which I have been copying.

How do I get the macro so it changes the column reference for the last 2?

Thanks

 

[Debra Dalgleish]

You could use a variable to select the columns, e.g.:

'====================
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Integer
Set ws1 = Sheets("SBS Graphs - Main")
Set ws2 = Sheets("Test")

ws2.Cells.ClearContents

For i = 1 To 10
Union(ws1.Cells(1, 1), ws1.Range(ws1.Cells(1, i * 2), _
ws1.Cells(1, i * 2 + 1))).EntireColumn.Copy _
Destination:=ws2.Cells(1, (i - 1) * 3 + 1)
Next i
'====================