VBA Copy a column to nth colums in Excel

[Coenieh]

Hi All,

I'm new to this blog, so I hope I follow the rules correctly. Thanks for accepting me.

I need help setting up a macro/VBA to copy a specific column (G) range "G1:G550", which contains data in some cells and formulas in the others others. The column needs to be copied to the next 50 columns to every 2nd column, starting from Column I then K then M etc.

I've tried the following code but it does not seem to work:

Sub ColCopy()


Dim cpval As Range
Dim lastRow As Long


With Worksheets("Sheet4")
    lastRow = .Cells(Rows.Count, "G").End(xlUp).Row
    Set cpval = .Range("G550:G" & lastRow)


    For colx = 9 To 50 Step 2
        .Range(.Cells(550, colx), .Cells(lastRow, colx)).Value = cpval.Value
    Next
End With
End Sub

I would appreciate any help. Please let me know should you require more information.

Thank You

 

[AlphaFrog]

What exactly does "but it does not seem to work" mean in this context. Be specific please.

 

[Akuini]

I need help setting up a macro/VBA to copy a specific column (G) range "G1:G550",

Because it is a fixed range 'Range("G1:G550")' then you don't need to find last row.

Sub ColCopy()

Dim cpval As Range
With Worksheets("Sheet4")
    Set cpval = .Range("G1:G550")

    For colx = 9 To 50 Step 2
        .Range(.Cells(1, colx), .Cells(550, colx)).Value = cpval.Value
    Next
End With
End Sub

 

[Coenieh]

Hi AlphaFrog,

It is not copying or pasting anything. When I change some of the parameters it will only copy the text and not the formulas but then does not paste to the intended colums.

 

[Coenieh]

Hi Akuini,

I have tried various options with the range as "G550:G" and "G1:G550"

.Range("G550:G" & lastRow)

But it does not work. Any idea of how I can copy column G and paste it into every 2nd column after e.g. I, K, M etc.

 

[Akuini]

Hi Akuini,

I have tried various options with the range as "G550:G" and "G1:G550"

.Range("G550:G" & lastRow)

But it does not work. Any idea of how I can copy column G and paste it into every 2nd column after e.g. I, K, M etc.

You mean it doesn't copy paste anything at all?
Actually the code I gave you works for me, I've tried it. Don't know why it don't work for you.
The data is in "sheet4", isn't it?

 

[Akuini]

I just realized that you want to copy "all" not just value.
In this case use this code below:
But again, the previous code should copy paste something i.e the value.

[COLOR=blue]Sub[/COLOR] ColCopy1[B]()[/B]
[COLOR=blue]Dim[/COLOR] colx [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]With[/COLOR] Worksheets[B]([/B][COLOR=brown]"Sheet4"[/COLOR][B])[/B]
    [COLOR=blue]For[/COLOR] colx [B]=[/B] [B][COLOR=crimson]9[/COLOR][/B] [COLOR=blue]To[/COLOR] [B][COLOR=crimson]50[/COLOR][/B] [COLOR=blue]Step[/COLOR] [B][COLOR=crimson]2[/COLOR][/B]
        [B].[/B]Range[B]([/B][COLOR=brown]"G1:G550"[/COLOR][B]).[/B]Copy [B].[/B]Range[B](.[/B]Cells[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] colx[B]),[/B] [B].[/B]Cells[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] colx[B]))[/B]
    [COLOR=blue]Next[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Coenieh]

Hi Akuini,

It copies the cells with text in it, but does not copy the formulas from the rest of the cells below those cells. Should I include a paste special?

 

[Coenieh]

Hi Akuini,

Thanks a million, your last version did the trick. Much appreciated.

 

[Akuini]

Hi Akuini,

Thanks a million, your last version did the trick. Much appreciated.

Your welcome & thanks for replying