VB Macro to Sort Columns in multiple workbooks

Hello,
I have an excel workbook with about 48 sheets. About 40 of those sheets are formatted the same way, and I need to sort from C2 to the end, E2 to the end, and G2 to the end. I have tried recording a macro, but it only works for one sheet, and it's hard to adapt because each sheet has a range of different length. After the sort, I then need to hide columns A,B,H,L, and M. Thanks for all the help!
indigo6Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
OK, assuming that Row 1 is where your headings are...


Sub SortThemAll()
    
    Dim ws As Worksheet
        
    For Each ws In ThisWorkbook.Worksheets
        With ws
            .Columns.Hidden = False
            .Rows.Hidden = False
            .[a1].Sort Key1:=.[c1], Key2:=.[e1], Key3:=.[g1], Order1:=xlAscending, _
                Order2:=xlAscending, Order3:=xlAscending, Header:=xlYes
            .Columns("A:B").Hidden = True
            .Columns("H:H").Hidden = True
            .Columns("L:M").Hidden = True
        End With
    Next
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
FaustulusCommented:
Are the columns in any one sheet all of the same length? If not, can you tell which is the longest column?
0
 
Patrick MatthewsCommented:
A sample file would be helpful
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
indigo6Author Commented:
The column lengths always vary. The longest column is 1319 rows long.
0
 
Patrick MatthewsCommented:
The column lengths always vary.

That's not a worry at all.  However...

I have an excel workbook with about 48 sheets. About 40 of those sheets are formatted the same way, ...

Which have to be sorted?  All worksheets?  Only the ~40 or so?  If all, then what sorting instructions should be used for the ~8 or so that are different?

...and I need to sort from C2 to the end, E2 to the end, and G2 to the end.

What columns on the worksheets are actually populated?  Are there header rows, or not?

This is why I asked for a sample file :)
0
 
indigo6Author Commented:
Let me see if I can make a sample file. It's private data, so I'm a bit leery about it.

Also, I can move the sheets that don't need to be sorted to another workbook, so let's go on the assumption that all sheets need to be sorted.

Yes, there are header rows. Columns A-O have data.

Thanks!
0
 
indigo6Author Commented:
This is perfect! Nice separation of statements to make it easy to edit for future use too!
0
All Courses

From novice to tech pro — start learning today.