Code to multiple sheets

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
Hi All,
Can you please take a gander and see what am I doing wrong here? Its just a code to hide columns based on a row that has "X" in place.
It works on the active sheet but doesn't go to the next ws. THanks in advance.

VBA Code:
Sub TEST_TEST()
Dim ws As Variant, Xrow As Long, LCol As Long

Application.ScreenUpdating = True

For Each ws In Worksheets(Array("Income Statement", "OCC"))

Xrow = 5
LCol = ws.Cells(4, Columns.Count).End(xlToLeft).Column

    With ws
        For x = 1 To LCol
            If Cells(Xrow, x) <> "X" Then
                Columns(x).Hidden = True
            Else
                Columns(x).Hidden = False
            End If
        Next x

End With

Next ws


Application.ScreenUpdating = False
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,611
Office Version
  1. 365
Platform
  1. Windows
You need to qualify the cells & columns with the worksheet, like
VBA Code:
Sub TEST_TEST()
Dim ws As Variant, Xrow As Long, LCol As Long

Application.ScreenUpdating = True

For Each ws In Worksheets(Array("Income Statement", "OCC"))

Xrow = 5
LCol = ws.Cells(4, Columns.Count).End(xlToLeft).Column

    With ws
        For x = 1 To LCol
            If ws.Cells(Xrow, x) <> "X" Then
                ws.Columns(x).Hidden = True
            Else
                ws.Columns(x).Hidden = False
            End If
        Next x

End With

Next ws


Application.ScreenUpdating = False
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,611
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,664
Messages
5,637,643
Members
416,979
Latest member
CapeCon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top