Vba to move columns with certain headers

[iuliamihaela]

Hi, Can you please help me with the following - I have an excel file that is being exported everyday, and the columns are either moved or more columns are added. I need a macro to arrange some of these columns in a specific order. For example: Initial columns order as per today: A B C D E F DSPID AssortID SKU SKUDescription Quantity ProjectName / Order always needed: A B C D E F DSPID ProjectName AssortID SKU SKUDescription Quantity. I have to "read" the headers of the columns and where it says "ProjectName", to move that whole column to B. Thank you very much! Iulia

[Aussiebear]

Please attach a sample workbook to overcome the following issues;

I have an excel file that is being exported everyday, and the columns are either moved or more columns are added.

Which columns are being moved? For example are any of the following columns being moved, DSPID AssortID SKU SKUDescription Quantity ProjectName / Order? Will they always be found within the first 6 columns?

Initial columns order as per today: A B C D E F DSPID AssortID SKU SKUDescription Quantity ProjectName / Order

Please don't mix column labels (Excel defined) and Column Headers (User defined) when trying to explain an issue.

always needed: A B C D E F DSPID ProjectName AssortID SKU SKUDescription Quantity.

Can I assume that the order of columns will always be the same, with the exception of ProjectName/Order which could be anywhere or should we be looking at making sure the order is always DSPID, ProjectName/Order, AssortID, SKU, SKUDescription, Quantity

I have to "read" the headers of the columns and where it says "ProjectName", to move that whole column to B.

Are we just moving the column ProjectName/Order to Column B?


Can you see where I'm going with this? To attach a file, click on Go Advanced, scroll down to Manage Attachments and follow the prompts from there please. When responding think about the points I have raised here, and we'll be only too happy to have a look at your query.

[iuliamihaela]

Hi,

I have attached a sample file example.xlsx - Sheet "Initial" contains the column headers in the order that is being exported, and Sheet "Final" contains the columns in the order I need. I have highlighted the columns that are being moved.

I need the macro to rearrange the columns based on column header and not based on column labels, because the order in the file exported (Initial) is changing constantly and this would require updating the macro with the new column labels very often.


Hope you can help.
Thank you very much,
Iulia

[Aussiebear]

Try the following on a sample worksheet:

Private Sub Reorder_Columns()
Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
arrColOrder = Array("DSPID", "ProjectName", "ProjectStatus", "AssortID", "SKU", "SKUDescription", "Quantity")
counter = 1
Application.ScreenUpdating = False
For ndx = LBound(arrColOrder) To UBound(arrColOrder)
Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If
Next ndx
Application.ScreenUpdating = True
End Sub

Note Insert into the sheet module you wish to run it on.

[iuliamihaela]

Hi, I have tried the code and it does the following - it moves column W in my file (which is ProjectName) to column B. But the rest of the columns remain in the same order. Should I do anything else? I wanted to attach the file I used for testing, but it the Manage Attachments button doesn't work (and also I cannot format this paragraph in order to look normal, with spaces)...Therefore, please find the order of the columns below:
DSPID AssortID SKU SKUDescription Quantity ProductVersion Language Platform Market ProductFormat OfferingType Config ActivationType PaymentType DistributionType EncodingType BOMTemplate Status AssortmentTargetEndDate AssortmentActualEndDate SKUCreationTargetEndDate SKUCreationActualEndDate ProjectName Project Status Retailer RetCountry Distributor DistCountry TechVendor TechCountry ECCID
PM

I am sorry for sending you the information like this. Hope you can help further. Thank you, Iulia

[bayern21]

Hi,
I have a similiar issue and can't get my Macro to work...

Right now I have a column called "057 - Interest Margin" as a header (row 1) in column AM. I want VBA to search for that value and then cut the entire column and move it to column S. If it can't find the column "057 - Interest Margin" VBA is supposed to insert a new column (column S) called "057 - Interest Margin".

Thanks
Stefan

[bayern21]

Hi,
I have a similiar issue and can't get my Macro to work...

Right now I have a column called "057 - Interest Margin" as a header (row 1) in column AM. I want VBA to search for that value and then cut the entire column and move it to column S. If it can't find the column "057 - Interest Margin" VBA is supposed to insert a new column (column S) called "057 - Interest Margin".

Thanks
Stefan

so what I have so far is the following:

For Each rng In Range("A1:BZ1")
If rng.Value = "057 - Interest Margin" Then
rng.EntireColumn.Cut
Sheets("Sheet1").Select
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
End If
Next rng

What I am missing right now is that a new column and a specific header is added in case "057 - Interest Margin" cannot be found.

I came up with this but it screws up everything because it inserts the column 18 times????


For Each Rng In Range("A1:BZ1")
If Rng.Value = "057 - Interest Margin" Then
Rng.EntireColumn.Cut
Sheets("Sheet1").Select
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Else:
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Range("S1").Select
ActiveCell.FormulaR1C1 = "057 - Interest Margin"
Range("S2").Select
End If
Next Rng