Another Hide Columns Question

[Dallas PM]

I am running Excel 2003 and need help with a macro. I have a table consisting
of Region, Stores and Monthly Sales. I wrote a macro to filter (hide) Stores
depending on the Region.

Region Store Sales City
Region1 Store1 $170K City1
Region1 Store2 $160K City2
Region2 Store1 $220K City1
Region2 Store3 $250K City1
Region3 Store1 $200K City2

In the following macro, the target value of A1 (Region) is located on
another worksheet, but I want to use that information to hide the Store
(column) that are not in the Region. When I run the macro, there is no change
in the results.

Any help would be greatly appreciated

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Range("A").EntireColumn.Hidden = False
If Target.Value = "Region1" Then
Range("A:B").EntireColumn.Hidden = True
Range("C").EntireColumn.Hidden = False
Else
Range("A:B").EntireColumn.Hidden = False
Range("C").EntireColumn.Hidden = True
End If
End If
End Sub

 

[Bernie Deitrick]

Copy the code below, right click the tab of the "another worksheet" where A1 is located, select
"View Code" and paste the code into the window that appears.

My code assumes that the sheet with the data table is named "Data", and that the table starts in
cell A1.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Worksheets("Data").Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Target.Value
End If
End Sub

 

[Dallas PM]

TY for the response. I am looking to view the profitability of each store
collectively and by region with the end result looking similar to:

Region1 (A1)

City1 City2
Store1 $170K
Store2 $160K


Region2 (A1)

City1
Store1 $220K
Store3 $250K


Region3 (A1)


City2
Store1
$200K


Region0 (A1)

City1 City2 City1 City2
Store1 $170K
Store2 $160K
Store1 $220K
Store3 $250K
Store1 $200K