I have this code that looks through each worksheet and rearranges the columns based on an array. The problem is, with each sheet the code moves my columns from starting in A1.
For example, sheet 1 starts in A1, sheet 2 starts in P1, sheet 3 starts in AE, and so forth. They all need to start in A1.
Also, on every sheet except sheet 1, it adds a blank column after each header, which I don't want.
Here is the code:
Sub RearrangeColumnsInAllWorksheets()
Dim arrColOrder As Variant
arrColOrder = Array("Company", "First Name", "Last Name", "Email", "Category", "Address", "Suite or Unit?", "Suite/Unit", "City", "Province", "Postal Code", "Phone", "Fax", "Website", "Service Areas", "Logo", "CONCAT")
Dim ndx As Long
Dim Found As Range
Dim Counter As Long
Counter = 1
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'loop through all worksheets
For ndx = LBound(arrColOrder) To UBound(arrColOrder)
Set Found = ws.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
ws.Columns(Counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
Counter = Counter + 1
End If
Next ndx
Next ws
Application.ScreenUpdating = True 'don't forget to turn it on again
End Sub
Thanks!