CODEWITHSUNDEEP
vbaexcel
sideshowbob
sideshowbob

Reputation: 316

Sort excel columns across sheets

I have an excel file with 10 sheets. The sheets all contain the same column headers but different data. I sorted the first sheet manually and now I want all the columns in the other sheets to match the first sheet's order, I can't do them all manually because it would take me forever. How can I make all the columns across the workbook in the same order based on the first sheet order? I know little about VBA so looking for some help.

Upvotes: 0

Views: 1143

Answers (2)

Horaciux
Horaciux

Reputation: 6477

Make sure you save the entire workbook before running a macro. There is no undo.

Hope this help:

Sub ColumnRearrangement()
  'Horaciux 2014-06-23
Dim nextLabel As String
Dim currentLabel As String

Dim TotalPages As Integer
Dim TotalColumns As Integer

TotalPages = 10
TotalColumns = 200

'Insert a blank column in each page
For p = 2 To TotalPages
    Sheets(p).Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
Next

For c = TotalColumns To 1 Step -1
    Sheets(1).Select

    'Debug.Print "-" & Cells(1, c).Text & "-" & Str(c)
    nextLabel = Cells(1, c).Text
    Sheets(2).Select
    For oldCulumn = 2 To TotalColumns + 1

        'Debug.Print Cells(1, oldCulumn).Text & "-" & Str(oldCulumn)
        currentLabel = Cells(1, oldCulumn).Text

        If currentLabel = nextLabel Then
            'Debug.Print currentLabel & "-" & Str(oldCulumn)
            Exit For
        End If
    Next

    For p = 2 To TotalPages
        Sheets(p).Select
        Columns(oldCulumn).Select
        Selection.Cut
        Columns("A:A").Select
        Selection.Insert Shift:=xlToRight
    Next
Next

For p = 2 To TotalPages
    Sheets(p).Select
    Range("A1").Select
Next

End Sub

Upvotes: 1

Astockwell
Astockwell

Reputation: 1536

If you're looking to repeat the re-ordering of columns, you should be able to record a simple macro for this, as long as the columns in all the sheets are in the same (wrong) order. Open the second sheet (since you already arranged the first), and do the following:

  1. Open the second sheet
  2. Click record macro, give it any name
  3. While it's recording, cut/insert the columns in the order you want within the second sheet
  4. Once done, click stop recording

Now you have a macro that you can open the rest of the pages, and it will repeat your re-ordering. Open each sheet and click View Macros, then run the one you just created. If you save the file, it will save the macro as well.

EDIT: Excellent point by @horaciux, be sure to save the entire workbook before running a macro, since there is no undo.

Upvotes: 0

Related Questions