Copying a Column Range to every nth column

[David Evans]

In one Legacy Worksheet I get to work on, I'd like to be able to copy a range (G9:G40) to every other column for 75 - I've searched for some generic code that would allow me flexibility to define the range to be copied and the "n'th column to be copied , but I'm striking out - any of you seen anything like it?

As usual I think the error is mine (P.O. causes more crashes than Machine Error ... sad but true) My flying gets worse on a Friday afternoon, to boot ...

 

[Luke M]

Here's a fairly generic macro, which I tried to include lots of comments on. Let me know if you have any quesstions. We could make all of the inputs set via InputBoxes, if you need a cleaner layout.

Sub CopyEveryNth()
Dim copyRange As Range
Dim firstCol As Long
Dim lastCol As Long
Dim myRow As Long
Dim nValue As Long
Dim xCounter As Long

'Which range are we copying?
Set copyRange = Range("G9:G40")

'Which column do we start copying? (give number)
firstCol = 7
'Which column to stop at?
lastCol = 75
'Define N as "copy to every Nth column"
nValue = 2
'Which row do we copy to?
myRow = 2

'OPTIONAL: Uncomment this next line if you want to copy to same row as CopyRange
'myRow = copyRange.Cells(1).Row


Application.ScreenUpdating = False
copyRange.Copy
For xCounter = firstCol To lastCol Step nValue
    ActiveSheet.Paste Cells(myRow, xCounter)
Next xCounter
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[David Evans]

Ninja Luke - the Excel Apostle!
Thank you very much for this - it saved me a lot of head scratching on a Friday afternoon - I was getting there, but I was on foot and you were on the Tardis ... Dr Chand-Who style