Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows and update linked cells | Excel Discussion (Misc queries) | |||
How to update links between wk sheets | Excel Worksheet Functions | |||
Updating, Deleting and inserting rows over two Excel Sheets | Excel Worksheet Functions | |||
Linking a cell to update when rows added | Excel Discussion (Misc queries) | |||
Auto update entire columns / rows??? | Excel Worksheet Functions |