0

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!

0m3r
  • 12,286
  • 15
  • 35
  • 71
maracleinc
  • 17
  • 3
  • move `Counter = 1` right below `For Each ws In ThisWorkbook.Worksheets` to reset the counter for every worksheet. – Pᴇʜ Nov 07 '18 at 13:30
  • @Pᴇʜ is really too fast ;) – Vincent G Nov 07 '18 at 13:33
  • @VincentG Actually not that fast: I wrote that answer [here](https://stackoverflow.com/questions/53176057/rearrange-columns-on-all-worksheets/53176192#53176192) and the OP asked that already in a comment (which provided not the essential information to understand what the issue was). So actually with a better explained question I immediately saw the issue here because I already knew that piece of code ... So I had some kind of advantage here ;) – Pᴇʜ Nov 07 '18 at 13:40

0 Answers0