Copy Column range values in Excel VBA

[Monty9]

Hello Guys,

I am trying to copy excel column values from the Left column to the immediate next right column. like in the screenshot. May I request your support for a code to copy Column A1:A6 to B1:B6 and then jump to column D1:D6 to be copied to E1:E6 and jump further to G1:G2 and so on till last column. The table structure is fixed with 3 columns as shown in screenshot and won't change.

Thanks

 

[Joe4]

Try this:

Sub MyCopy()

    Dim lc As Long
    Dim c As Long
 
    Application.ScreenUpdating = False
   
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Copy blocks of data
    c = 1
    Do
'       Copy block of data, from rows 1-6
        Range(Cells(1, c), Cells(6, c)).Copy Cells(1, c + 1)
'       Increment column counter by 3
        c = c + 3
'       If column counter greater than last column, exit
        If c > lc Then Exit Do
    Loop

    Application.ScreenUpdating = True
   
End Sub

 

[Monty9]

Try this:

Sub MyCopy()

    Dim lc As Long
    Dim c As Long
 
    Application.ScreenUpdating = False
  
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
  
'   Copy blocks of data
    c = 1
    Do
'       Copy block of data, from rows 1-6
        Range(Cells(1, c), Cells(6, c)).Copy Cells(1, c + 1)
'       Increment column counter by 3
        c = c + 3
'       If column counter greater than last column, exit
        If c > lc Then Exit Do
    Loop

    Application.ScreenUpdating = True
  
End Sub

Thank you Joe. Works exactly as required.

 

[Joe4]

You are welcome!