Arrange Columns

[Thomas Kuriakose]

Respected Sirs,

We have two workbooks with 32 columns of data each. The first workbook columns need to be arranged as per columns in final workbook.

I have input the sample in two worksheets.

First work sheet = has columns C1 to C32 (A2:AF2)

The final work book = the columns from first workbook to be arranged in sequence A5 to AF5 for the columns C1 to C32.

Kindy find attached the sample for your reference -

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

Copy the code in the attached file to your working file , and run the macro named ConvertFirstToFinal.

As it stands , the code does only a cut and paste. When it has completed execution , the worksheet named First will not contain any data , while the worksheet named Final will contain the rearranged columns of data.

Make a backup of your workbook before you run the macro.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided for this query.

Much appreciated.

Sir, one question, if we need to run this from two workbooks, kindly advise the code. The actual data is in two workbooks.

Or is it good to copy the two workbook data to two tabs and run the code.

Thank you very much once again.

with regards,
thomas

 

[NARAYANK991]

Hi ,

I assume both the workbooks will be open when you run the macro.

Post the name of the other workbook.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for your guidance on this.

The first workbook is "Master data" and the final workbook is "Aligned data".

Thank you very much for your support once again,

with regards,
thomas

 

[NARAYANK991]

Hi ,

Try this code :

Public Sub ConvertFirstToFinal()
           Const FINALORDER = "21,22,20,19,32,7,6,27,23,24,25,26,9,10,11,12,13,14,15,16,17,28,29,30,31,4,3,1,2,18,5,8"
        
           Dim orderarray As Variant
           orderarray = Split(FINALORDER, ",")
        
           For i = 0 To UBound(orderarray, 1)
               Me.Worksheets("First").Columns(Val(orderarray(i))).Cut
               Workbooks("Aligned data").Worksheets("Final").Columns(1).Offset(, i).Insert xlToRight
           Next
End Sub

I assume the workbooks have worksheets named First and Final in them ; if not , you will need to change the code.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much once again for the revised code to accommodate the workbooks.

Much appreciated,

with regards,
thomas

 

[Monty]

NARAYANK SIR.

Excellent code..

Quick question on this as we mentioned in array column numbers....What-if the column number are not dynamic.

Can we go head with column names instead column numbers.

Hi ,

Try this code :

Public Sub ConvertFirstToFinal()
           Const FINALORDER = "21,22,20,19,32,7,6,27,23,24,25,26,9,10,11,12,13,14,15,16,17,28,29,30,31,4,3,1,2,18,5,8"
      
           Dim orderarray As Variant
           orderarray = Split(FINALORDER, ",")
      
           For i = 0 To UBound(orderarray, 1)
               Me.Worksheets("First").Columns(Val(orderarray(i))).Cut
               Workbooks("Aligned data").Worksheets("Final").Columns(1).Offset(, i).Insert xlToRight
           Next
End Sub

I assume the workbooks have worksheets named First and Final in them ; if not , you will need to change the code.

Narayan

 

[Tim Hanson]

Hello Monty, I use this for rearranging columns with header names

'Sheet Name
'Array of Header names
Sub iRearangeCols()

  fReArangeCols "Elements", Array("Id", "Label", "Type", "Email")

End Sub

'Rearange Columns
Function fReArangeCols(shtName As String, HeaderNames As Variant)
Dim ws As Worksheet
Dim colFrom As Long
Dim L As Long

Set ws = ThisWorkbook.Sheets(shtName)

On Error GoTo Skipit
For L = 0 To UBound(HeaderNames)
    colFrom = ws.Rows(1).Find(HeaderNames(L), , xlValues, xlWhole).Column
    If L + 1 <> colFrom Then ws.Columns(colFrom).Cut: ws.Columns(L + 1).insert
Skipit:
Next

'ActiveSheet.UsedRange.Offset(, 1).ClearContents
On Error GoTo 0
End Function

 

[Chirag R Raval]

Dear All

Amazing, very Interesting article & excellent work found for re-arranging columns as per Column Address Or Columns Number found here. if repetitive study it from first to last, each time , explore more quata to deeper understanding things .

http://www.excelfox.com/forum/showthread.php/493-Swapping-(Rearranging)-Multiple-Columns-of-Data

Hope users, who seek for this requirement, found very helpful content here.

Regards,

Chirag Raval

 

[Marc L]

As you can already see in many threads of this forum like for fixed columns
How to copy data if Range of that data given a cells using Macro
or for conditional columns VLOOKUP USING VBA …

Another sample here when columns number may vary.
​