VBA to copy one range in worksheet to multiple ranges on same worksheet

[erickguz]

Hi,

I have formulas from A5:A100 that I would like to paste in e5:e100 all the way down to CD5:cd100.

The following example is wat i have to paste to 3 columns from rows 1 - 23. Do I need to type in the range for the next 50 columns that I need pasted or is there something shorter? Again, I have to paste out to every forth columns up until CD1:CD23, and beyond. ALSO, I NEED TO COPY FORMULAS, NOT VALUES.

Sub Paste_Range()

'Copy and Paste a Range of Cells
Range("A1:A23").Copy Range("e1:e23")
Range("A1:A23").Copy Range("i1:i23")
Range("A1:A23").Copy Range("q1:q23")



Application.CutCopyMode = False

End Sub

 

[My Aswer Is This]

Try this:

If you want to start with column E and then column I and so only every fourth column we would end at column CC not CD

Sub My_Sub()
'Modified 6/27/18 1:00 AM EDT
Dim i As Long
Application.ScreenUpdating = False
Range(Cells(5, 1), Cells(100, 1)).Copy
    For i = 5 To 82 Step 4
        Range(Cells(5, i), Cells(100, i)).PasteSpecial xlPasteFormulas
    Next
Application.ScreenUpdating = True

End Sub

 

[erickguz]

Excellent. Worked out perfectly. Amazing how vba really shortens the process. And thank you so much for helping me...
-Erick

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Excellent. Worked out perfectly. Amazing how vba really shortens the process. And thank you so much for helping me...
-Erick