macro to copy columns from one sheet to another sheet

  • Thread starter Thread starter beginner
  • Start date Start date
B

beginner

I have over 120 columns on one sheet and I would like to copy and
paste every 3rd column onto another sheet. I started to "record"
macro, but was wondering if there was a way to write code for this?

so I have something like this:


x y z x y z ...
1
1
2
3
8
7,etc..


I would like to copy all of the "x" column values onto another sheet.
Any solutions?


Thanks!
 
One way change destination sheet and fire from active sheet
Sub copycolumns1()
With Sheets("sheet20")
For i = 1 To Cells(1, Columns.Count) _
..End(xlToLeft).Column Step 3
Columns(i).Copy .Cells(1, .Cells(1, Columns.Count) _
..End(xlToLeft).Column + 1)
Next i
End With
End Sub
 
One way change destination sheet and fire from active sheet
Sub copycolumns1()
With Sheets("sheet20")
For i = 1 To Cells(1, Columns.Count) _
.End(xlToLeft).Column Step 3
Columns(i).Copy .Cells(1, .Cells(1, Columns.Count) _
.End(xlToLeft).Column + 1)
Next i
End With
End Sub







- Show quoted text -
Click to expand...

I tried it (as is) but it did not work. Is there something I will
need to modify to fit my application specifically?

For example, I got an error with the below:

With Sheets("sheet20") ||I changed the name from "sheet20" to the
current sheet I am working in...this is probably wrong.

Also got an error here:
Columns(i).Copy .Cells(1, .Cells(1, Columns.Count) _ ||the macro
also stopped when I got here as .Cells was not specified

Basically I would like to copy the columns (every 3rd column) from
sheet 1 and put them in sheet 2 automatically.
 
Hi
Yes ,you need to change the sheet name to yoursYou must execute the macro from your dataSheet and the top row must be filled
with data.
HTH
Cimjet

One way change destination sheet and fire from active sheet
Sub copycolumns1()
With Sheets("sheet20")
For i = 1 To Cells(1, Columns.Count) _
.End(xlToLeft).Column Step 3
Columns(i).Copy .Cells(1, .Cells(1, Columns.Count) _
.End(xlToLeft).Column + 1)
Next i
End With
End Sub







- Show quoted text -
Click to expand...

I tried it (as is) but it did not work. Is there something I will
need to modify to fit my application specifically?

For example, I got an error with the below:

With Sheets("sheet20") ||I changed the name from "sheet20" to the
current sheet I am working in...this is probably wrong.

Also got an error here:
Columns(i).Copy .Cells(1, .Cells(1, Columns.Count) _ ||the macro
also stopped when I got here as .Cells was not specified

Basically I would like to copy the columns (every 3rd column) from
sheet 1 and put them in sheet 2 automatically.
 
Just a bit alteration in the nice code.Pls try.

Sub CopyColumns1()

Dim col As Long

col = Cells(1, Columns.Count).End(xlToLeft).Column
With Sheets("sheet20")
For i = 1 To col Step 3
Columns(i).Copy .Cells(1, .Cells(1, .Columns.Count) _
.End(xlToLeft).Column + 1)
Next i
End With
End Sub
 
Back
Top