I have a workbook with several worksheets. I am trying to write a VBA
macro that would look at row 5 to see if any cells indicate "Hide
column". If it does, it would hide all the columns indicating "Hide
column" on this worksheet. Then it would continue the same process to
the next worksheet, and the next one ...

I currently have the macro working for the individual worksheet (see
below), but I would like to have it work to do all the worksheets of
the workbook.

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For a = 5 To 200
If Range("A5").Offset(0, a).Value = "Hide column" Then Range
("A5").Offset(0, a).EntireColumn.Hidden = True
Next a
Application.ScreenUpdating = True

Thanks in advance for your help.

Hi

This should do it:

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each sh In ThisWorkbook.Sheets
For a = 5 To 200
If sh.Range("A5").Offset(0, a).Value = "Hide column" Then
sh.Range("A5").Offset(0, a).EntireColumn.Hidden = True
End If
Next a
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards,
Per

"Norvascom" skrev i meddelelsen
...
I have a workbook with several worksheets. I am trying to write a VBA
macro that would look at row 5 to see if any cells indicate "Hide
column". If it does, it would hide all the columns indicating "Hide
column" on this worksheet. Then it would continue the same process to
the next worksheet, and the next one ...

I currently have the macro working for the individual worksheet (see
below), but I would like to have it work to do all the worksheets of
the workbook.

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For a = 5 To 200
If Range("A5").Offset(0, a).Value = "Hide column" Then Range
("A5").Offset(0, a).EntireColumn.Hidden = True
Next a
Application.ScreenUpdating = True

Thanks in advance for your help.

On Feb 1, 2:25*am, "Per Jessen" wrote:
Hi

This should do it:

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each sh In ThisWorkbook.Sheets
* * For a = 5 To 200
* * * * If sh.Range("A5").Offset(0, a).Value = "Hide column" Then
* * * * * * sh.Range("A5").Offset(0, a).EntireColumn.Hidden = True
* * * * End If
* * Next a
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards,
Per

"Norvascom" skrev i ...



I have a workbook with several worksheets. I am trying to write a VBA
macro that would look at row 5 to see if any cells indicate "Hide
column". If it does, it would hide all the columns indicating "Hide
column" on this worksheet. Then it would continue the same process to
the next worksheet, and the next one ...

I currently have the macro working for the individual worksheet (see
below), but I would like to have it work to do all the worksheets of
the workbook.

Sub HideColumn()
* *Application.ScreenUpdating = False
* *Application.Calculation = xlCalculationManual
* *For a = 5 To 200
* *If Range("A5").Offset(0, a).Value = "Hide column" Then Range
("A5").Offset(0, a).EntireColumn.Hidden = True
* *Next a
* *Application.ScreenUpdating = True

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

Thanks, it works perfectly.