VBA to Shift Table Layout from right to left in Excel

[happydz]

Hi everyone,

I need help with a VBA code to shift an existing table from right to left in Excel. I’ve attached two screenshots for reference:

• Screenshot 1: Shows the current layout, where the columns "Number", "Name", and "Salary" are in columns A, B, and C.
• Screenshot 2: Shows the desired layout, where these columns are moved to columns B, F, and F, with the "Month" header remaining above them.
  P.S: I know the trick that reverse the whole sheet from right to left and VS but I only want to reverse a table in this way.

Could someone help me with a VBA solution to achieve this?

Thanks in advance for any assistance!

 

[Skyybot]

Test this on a COPY of your Workbook. Past this code in the Sheet code module of the Sheet that you want to affect. Select whole Columns, not just a Range. It assumes the EXACT layout of the photo you posted.

Option Explicit

Sub ColumnsSwap()
Dim oRng As Range, nRng As Range, ooRng(), nnRng()
Set oRng = Application.InputBox("Select first Column to swap.", "Swap", Type:=8)
Set nRng = Application.InputBox("Select second Column to swap.", "Swap", Type:=8)
Debug.Print oRng.Columns.Column
Set oRng = Range(Me.Cells(2, oRng.Columns.Column), Me.Cells(Me.Rows.End(xlDown).Row, _
    oRng.Columns.Column))
Set nRng = Range(Me.Cells(2, nRng.Columns.Column), Me.Cells(Me.Rows.End(xlDown).Row, _
    nRng.Columns.Column))
ooRng = oRng
nnRng = nRng
nRng.Value = ooRng
oRng.Value = nnRng
End Sub

 

[Peter_SSs]

Try this in a standard module with the relevant sheet as the active sheet.
Again, use a copy of your workbook

Sub MoveAndSwap()
  Dim i As Long, cols As Long
  
  With Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    cols = .Columns.Count
    For i = 1 To .Columns.Count
      .Columns(i).Cut Destination:=.Cells(1, 2 * cols + 2 - i)
    Next i
    .Rows(0).Cut Destination:=.Cells(0, cols + 2)
  End With
End Sub