Hide rows and update fileds in different sheets
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.Hidde n = False ElseIf .Address = "$B$3" And UCase(.Value) = "AMERICA" Then Worksheets("Sheet5").Rows("62:76").EntireRow.Hidde n = False Worksheets("Sheet5").Rows("62").EntireRow.Hidden = True ElseIf .Address = "$B$3" And UCase(.Value) = "ASIA" Then Worksheets("Sheet5").Rows("62:76").EntireRow.Hidde n = False Worksheets("Sheet5").Rows("64:69").EntireRow.Hidde n = 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 |
Hide rows and update fileds in different sheets
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.Hidde n = False ElseIf Worksheets("Sheet1").Range("$B$3").Value = "ASIA" Then Worksheets("Sheet5").Rows("55:76").EntireRow.Hidde n = False Worksheets("Sheet5").Rows("55:60").EntireRow.Hidde n = True and so on.. End If End Sub "Carlos Canstatt" wrote: 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.Hidde n = False ElseIf .Address = "$B$3" And UCase(.Value) = "AMERICA" Then Worksheets("Sheet5").Rows("62:76").EntireRow.Hidde n = False Worksheets("Sheet5").Rows("62").EntireRow.Hidden = True ElseIf .Address = "$B$3" And UCase(.Value) = "ASIA" Then Worksheets("Sheet5").Rows("62:76").EntireRow.Hidde n = False Worksheets("Sheet5").Rows("64:69").EntireRow.Hidde n = 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 |
All times are GMT +1. The time now is 05:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com