Hide rows and update fileds in different sheets

[Carlos Canstatt]

Folks,
I have a macro which is obvioulsy wrong. This is what it does:

The event occurs in Sheet1 when I change the region name on Data validation
list (America/Europe/ASIA..etc) ($B$3). Based on that change, several fields
with data validation will change the source list (America list, Europe
list..etc). But reminds the old selction until you select the drop down list
and chose a country of the new list.

I did a macro that chose the first country of the list assigend to several
cell with data validation in different sheets. That works fine. But...

Also when I chose a region in $B$3 I need to hide "in other sheet" the rows
that contains the regions not selected (or all if regions=Global). I modified
the fist macro to make this happen too, but it doesn't work.

Can you take a look?:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$B$3" And UCase(.Value) = "GLOBAL" Then
Worksheets("Sheet5").Rows("59:76").EntireRow.Hidden = False
ElseIf .Address = "$B$3" And UCase(.Value) = "AMERICA" Then
Worksheets("Sheet5").Rows("62:76").EntireRow.Hidden = False
Worksheets("Sheet5").Rows("62").EntireRow.Hidden = True
ElseIf .Address = "$B$3" And UCase(.Value) = "ASIA" Then
Worksheets("Sheet5").Rows("62:76").EntireRow.Hidden = False
Worksheets("Sheet5").Rows("64:69").EntireRow.Hidden = True
ElseIf Target.Address = "$B$3" Then
Worksheets("Sheet4").Range("B7:B127").Value =
Application.WorksheetFunction _
.Index(Sheets("SHeet2").Range(Target.Value), 1)
Worksheets("Sheet1").Range("B9:B29").Value =
Application.WorksheetFunction _
.Index(Sheets("SHeet2").Range(Target.Value), 1)
Worksheets("Purch. M&S").Range("A16:A19").Value =
Application.WorksheetFunction _
.Index(Sheets("SHeet2").Range(Target.Value), 1)
End If
End With
End Sub

THANKS A LOT IN ADVANCE,
Carlos

 

[Carlos Canstatt]

Folks,
Just answering to myself in case someone else is trying to answer this. I
just found the way to resolve it.. and I did it using usefull information
from this forum that I´ve just found.. so Thanks a lot anyway by make this
place so good!!!!!.
This one works fine:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Worksheets("Sheet4").Range("B7:B127").Value =
Application.WorksheetFunction _
.Index(Sheets("Sheet2").Range(Target.Value), 1)
Worksheets("Sheet1").Range("B9:B29").Value =
Application.WorksheetFunction _
.Index(Sheets("Sheet2").Range(Target.Value), 1)
Worksheets("Sheet3").Range("A16:A19").Value =
Application.WorksheetFunction _
.Index(Sheets("Sheet2").Range(Target.Value), 1)
Worksheets("Sheet3").Range("A25:A30").Value =
Application.WorksheetFunction _
.Index(Sheets("Sheet2").Range(Target.Value), 1)
Worksheets("Sheet3").Range("A36:A47").Value =
Application.WorksheetFunction _
.Index(Sheets("Sheet2").Range(Target.Value), 1)
ElseIf Worksheets("Sheet1").Range("$B$3").Value = "GLOBAL" Then
Worksheets("Sheet5").Rows("55:76").EntireRow.Hidden = False
ElseIf Worksheets("Sheet1").Range("$B$3").Value = "ASIA" Then
Worksheets("Sheet5").Rows("55:76").EntireRow.Hidden = False
Worksheets("Sheet5").Rows("55:60").EntireRow.Hidden = True
and so on..
End If
End Sub