Copying Column Ranges within a for loop

Question

I am running the following code which works fine, but I cannot find any solutions to simplify it. Have tried to rearrange the syntax in many different ways to shorten the code to two lines but they don't seem to work, can anyone advise on how to simplify this code:

'export results to columns
Worksheets(analysis_sheet & " Analysis").Columns(9).Copy Columns(9 + (5 * (i - 6)))
Worksheets(analysis_sheet & " Analysis").Columns(10).Copy Columns(10 + (5 * (i - 6)))
Worksheets(analysis_sheet & " Analysis").Columns(11).Copy Columns(11 + (5 * (i - 6)))
Worksheets(analysis_sheet & " Analysis").Columns(12).Copy Columns(12 + (5 * (i - 6)))

Thanks!

user_1140579 · Answer

...different ways to shorten the code to two lines...

Here is a 1 liner which will copy from Worksheets(analysis_sheet & " Analysis") to ActiveSheet

Worksheets(analysis_sheet & " Analysis").Columns("I:L").Copy Columns(9 + (5 * (i - 6)))

If you are copying to the same sheet then change the above code to

Worksheets(analysis_sheet & " Analysis").Columns("I:L").Copy _
Worksheets(analysis_sheet & " Analysis").Columns(9 + (5 * (i - 6)))

EDIT

That won't work because the OP is copying from columns 9 to 12 – freeflow 16 mins ago

No. In the term Columns(9/10/11/12 +(5*(i-6))) how does excel know how to update the 9 to 10,11,12 – freeflow 11 mins ago

Let's say the value of i is 10 so 9 + (5 * (i - 6)) = 29. Agreed? So Col I (9) will be pasted in Col AC (29). Similarly Col J (10) will be posted in Col AD (30) as 10 + (5 * (10 - 6)) = 30 and so on...

These two pieces of the code will so the same thing

Sub SampleA()
    Dim i As Long
    
    i = 10
    Sheet1.Columns(9).Copy Columns(9 + (5 * (i - 6)))
    Sheet1.Columns(10).Copy Columns(10 + (5 * (i - 6)))
    Sheet1.Columns(11).Copy Columns(11 + (5 * (i - 6)))
    Sheet1.Columns(12).Copy Columns(12 + (5 * (i - 6)))
End Sub

Sub SampleB()
    Dim i As Long
    
    i = 10

    Sheet1.Columns("I:L").Copy Sheet1.Columns(9 + (5 * (i - 6)))
End Sub

Copying Column Ranges within a for loop

1 Answers