CODEWITHSUNDEEP
vbaexcel
John
John

Reputation: 21

Excel - hide the same columns across multiple workbooks

I need to hide the same columns across several workbooks.

For example, I want to hide columns B, C, F, H, I, J, K, R, Q, AA, etc, and I need to hide those same columns in many different workbooks quickly upon opening these workbooks. In other words,

I need to create a custom view that I can apply to any workbook I open, quickly.

I am using Excel 2016.

Thanks!

Upvotes: 2

Views: 2871

Answers (2)

Samuel Hulla
Samuel Hulla

Reputation: 7099

You can use the For Each loop to loop through the collection of Workbooks and Worksheets respectively and repeat an action "for each" and every one of them.

Private Sub hide_columns()

   Dim wb As Workbook
   Dim ws As Worksheet

   For Each wb In Workbooks ' loop through all workbooks
     For Each ws In wb.Worksheets ' loop through all worksheets in workbooks
        ws.Columns("C").EntireColumn.Hidden = True
        ws.Columns("H:K").EntireColumn.Hidden = True
        ' - add the rest of the columns here 
     Next ws
   Next wb

End Sub

Upvotes: 1

oxwilder
oxwilder

Reputation: 767

You can use a workbook open event to set the properties of the columns you want:

Private Sub Workbook_Open()
Range("B:D,F:F").EntireColumn.Hidden = True

End Sub

Upvotes: 1

Related Questions