Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

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!
0
indigo6
Asked:
indigo6
  • 3
  • 3
1 Solution
 
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
 
indigo6Author Commented:
The column lengths always vary. The longest column is 1319 rows long.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Patrick MatthewsCommented:
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
 
indigo6Author Commented:
This is perfect! Nice separation of statements to make it easy to edit for future use too!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now