Rearrange the Columns

[vmjan02]

I have 2 sheets names "Ref1" and "WFMS-Dump" in "Ref1" sheet ther eis a list of header names from H2 till the last non blank cell, and with respect to the same headers need to be shifted or reaggnaged in sheet WFMS.

er:- if H2 in Ref sheet header name is in row no 2, then it should search the row 2 header name in sheet WFMS and after found sound be cut and placed in A:A in same sheet WFMS and loop till tbe last non blank cell of column H in Ref sheet.

I hoep I am clear.

this is in Ref sheet H2 till the last non blank cell

Sr.NO​	Column Header​	Rearrange to Column​
1​	INCIDENT_NUMBER​	A​
2​	INCIDENT_ID​	B​
3​	CUSTOMER_TICKET_NUMBER​	C​
4​	RTTS_TICKET_NUMBER​	D​
5​	PROJECT_NUMBER​	E​
6​	TASK_NUMBER​	F​
7​	SEVERITY​	G​
8​	CREATED_BY​	H​
9​	PP_Dept​	I​
10​	FAULT_REPORTED_TYPE​	J​
11​	NETWORK​	K​
12​	TECHNOLOGY​	L​
13​	INCIDENT_TYPE_NAME​	M​
14​	Fault_Area​	N​
15​	Fault_Sub_Area​	O​
16​	INCIDENT_CREATION_DATE​	P​
17​	INCIDENT_LAST_UPDATE_DATE​	Q​
18​	CLOSE_DATE​	R​
19​	INCIDENT_STATUS_NAME​	S​
20​	TASK_TYPE_NAME​	T​
21​	PP_Task_Reverse_Index_ByTaskID​	U​
22​	TASK_CREATION_DATE​	V​
23​	TASK_LAST_UPDATE_DATE​	W​
24​	ACTUAL_START_DATE​	X​
25​	ACTUAL_END_DATE​	Y​
26​	TASK_STATUS_NAME​	Z​
27​	ORGANIZATION_NAME​	AA​
28​	LC_NO_SLOT​	AB​
29​	WC_NAME​	AC​
30​	CS_RESOLUTION_CODE​	AD​
31​	RESOLUTION_CODE​	AE​
32​	REASON_CODE​	AF​
33​	CANCEL_REASON_CODE​	AG​
34​	COMPLAINT_OPEN_REASON​	AH​
35​	LAST_DEACTIVATION_REASON​	AI​
36​	Deactivate_Reason​	AJ​
37​	PROBLEM_CODE​	AK​

 

[alansidman]

Use the transpose UI in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Rearrange to Column"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

 

[vmjan02]

Use the transpose UI in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Rearrange to Column"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

Need it in VBA as I am not sure which excel version the end user will be used.

 

[alansidman]

FYI-- Power Query is an addin for 2010 and 2013 and is native to all other subsequent versions.

 

[kevin9999]

Do I understand you correctly in that you have those column headings already in sheet WFMS-Dump but they're in a different order than the list you have in sheet Ref1? And that you want them sorted into the order you have on Ref1 range H2:H38? If so, please try the following code on a copy of your workbook. I've assumed your headings are in row 1 of the WFMS-Dump sheet.

Option Explicit
Sub Rearrange_Columns()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Ref1")
    Set ws2 = Worksheets("WFMS-Dump")
    
    Dim a() As String, rng As Range
    Set rng = ws1.Range("H2", ws1.Cells(Rows.Count, "H").End(xlUp))
    a = Split(Join(Application.Transpose(rng), ","), ",")
    
    Application.AddCustomList ListArray:=a
    With ws2.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(ws2.Cells(1, 1), ws2.Cells(1, ws2.Cells(1, Columns.Count).End(xlToLeft).Column)), _
        CustomOrder:=Application.CustomListCount
        .SetRange ws2.Range("A1").CurrentRegion
        .Orientation = xlLeftToRight
        .Apply
    End With
End Sub

 

[vmjan02]

Hi Kevin,

You are right and thank you very much its is perfect and working all correct.

 

[kevin9999]

Happy to help, and thanks for the feedback