Rearrange of columns

[vmjan02]

I have this code and nost sure why its not rearragne the columns, the problem is it not arraning the IP Address column not sure why.

Any reason, as why it snot rearraing the columns are per the list in Ref1 sheet column E starting from 2

Sub RearragneColumnsCopperADTRAN()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
''''
''''  Rearrange Columns in "Copper ADTRAN" from Ref1 sheet column E
''''
''''
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Ref1")
    Set ws2 = Worksheets("Copper ADTRAN")

    ' Assuming you want to rearrange columns based on the order in Ref1 column E
    Dim orderRange As Range
    Set orderRange = ws1.Range("E2", ws1.Cells(Rows.Count, "E").End(xlUp))

    ' Create a dictionary to store the order of columns
    Dim columnOrder As Object
    Set columnOrder = CreateObject("Scripting.Dictionary")

    ' Populate the dictionary with column order
    Dim i As Integer
    For i = 1 To orderRange.Rows.Count
        columnOrder(orderRange.Cells(i, 1).Value) = i
    Next i

    ' Rearrange columns based on the order in the dictionary
    Dim ws2LastColumn As Integer
    ws2LastColumn = ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column

    For i = 1 To ws2LastColumn
        Dim columnName As String
        columnName = ws2.Cells(1, i).Value

        If columnOrder.Exists(columnName) Then
            Dim targetIndex As Integer
            targetIndex = columnOrder(columnName)

            ' Check if the column needs to be moved
            If i <> targetIndex Then
                ws2.Columns(i).Cut
                ws2.Columns(targetIndex).Insert Shift:=xlToRight
            End If
        End If
    Next i
End Sub

Golden Automation Report V1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Item type	Position	IP Address	Name	NE Type/Release	Software revision	Serial number	Hardware item number	Hardware revision	Firmware revision	Vendor
2	NE		10.74.76.57	MMRSTRDEE_AMSLE-A_Switched-off-copper sunset TT#PR00025933944	/						ADTRAN Copper
3	Rack	1	10.74.76.57		/						ADTRAN Copper
4	NE		10.74.84.3	MBGDYKHCH_CBABC-A	hiX5635 / 5.6H	saps.hix56xxsr56.04.002.cp.001					ADTRAN Copper
5	Rack	1	10.74.84.3		hiX5635 / 5.6H						ADTRAN Copper
6	Shelf	1	10.74.84.3	G1100:2G:E	hiX5635 / 5.6H		LBADTN1427AA008	S50028-B3102-A1-	8	not available	ADTRAN Copper
7	Module	101	10.74.84.3	VPLU:72P2:E	hiX5635 / 5.6H	she_sr5.6_iu-vplu-b2_o.006	LBADTN1435AJ065	S50028-Q2062-B2-	23	v11_26_03_08 / bl 2.1.0	ADTRAN Copper
8	Module	102	10.74.84.3	VPLU:72P2:E	hiX5635 / 5.6H	she_sr5.6_iu-vplu-b2_o.006	LBADTN1435AJ348	S50028-Q2062-B2-	23	v11_26_03_08 / bl 2.1.0	ADTRAN Copper
9	Module	103	10.74.84.3	VPLU:72P2:E	hiX5635 / 5.6H	she_sr5.6_iu-vplu-b2_o.006	LBADTN1435AJ440	S50028-Q2062-B2-	23	v11_26_03_08 / bl 2.1.0	ADTRAN Copper
10	Module	104	10.74.84.3	VPLU:72P2:E	hiX5635 / 5.6H	she_sr5.6_iu-vplu-b2_o.006	LBADTN1437AR001	S50028-Q2062-B2-	23	v11_26_03_08 / bl 2.1.0	ADTRAN Copper
11	Module	109	10.74.84.3	CXUB21GE:4:E	hiX5635 / 5.6H	she_sr5.6_cxu-b2_o.228	LBADTN1419AF023	S50028-Q3120-B1-	14	cxu_xx-b.V09	ADTRAN Copper
Copper ADTRAN

after running output

List is here column E sheet Ref1 starting from row no 2

 

[ExcelToDAX]

How about trying Power Query. Assuming your table name is called Table1, you could do the following:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Reordered Columns" = Table.ReorderColumns(Source,{"Name", "NE Type/Release", "IP Address", "Software revision", "Vendor", "Item type", "Position", "Serial number", "Hardware item number", "Hardware revision", "Firmware revision"})
in
    #"Reordered Columns"

 

[vmjan02]

no i am not use to power Query, vba is better for now

 

[ExcelToDAX]

Why can't you use Power Query?

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.