Macro to group / ungroup columns on multiple sheets

[koskesh]

Hi all,

I am not an macro expert and need some help.

I have a workbook with approx. 30 sheets.
The good thing is, that all sheets have the same layout.

I want to group following columns on all sheets:

D,E,F (1 group)
H (1 group)
O,P,Q (1 group)
S (1 group)
Z,AA,AB (1 group)
AD (1 group)

Is this possible via vba?

Thanks!

 

[Hui]

Kokesh

Sub Group_Sheets()
Dim ws As Worksheet

For Each ws In Worksheets
  ws.Select
  Columns("D:F").Group
  Columns("H").Group
  Columns("O:Q").Group
  Columns("S").Group
  Columns("Z:AB").Group
  Columns("AD").Group
Next ws

End Sub

 Sub UnGroup_Sheets()
Dim ws As Worksheet

For Each ws In Worksheets
  ws.Select
  Columns("D:F").Ungroup
  Columns("H").Ungroup
  Columns("O:Q").Ungroup
  Columns("S").Ungroup
  Columns("Z:AB").Ungroup
  Columns("AD").Ungroup
Next ws

End Sub

 

[koskesh]

Thank you Hui!

 

[Hui]

I just updated the post, so it is now simplified and answers the 2 questions

 

[mejenn]

Hello,

I have a similar request. I believe its simple but I can't seem to make it work. I have 8 worksheets with columns grouped on each sheet. I would like to create a macro button for an admin to be able to ungroup and regroup all of the columns at once. I found this >>>
Sheets("YourSheet").Outline.ShowLevels 8 <<< but sadly, I can't get it to work.

8 WORKSHEETS
CUR-15151
CUR-15176
CUR-15177
CUR-15267
CUR-15273
CUR-15286
CUR-22018
CUR-22621

 

[alanderson]

Hi - I'm a new member and understand the code above. Is it possible to ungroup a set of contiguous columns, do some formatting (I have the code for the formatting) and then re-group the columns all in the same macro? We have 40+ workbooks with multiple tabs that are updated on a monthly basis and it's time consuming to manually ungroup the columns tab by tab, run the formatting macro and then re-group each tab. Thanks for your help.