Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
Hi All I have various macro's that all work correctly when run independently. I want to run the correct macro for a certain area i.e. North West, when it is selected from a drop down menu in B2. I have had some advice already & have got the following macro "copyregion" which should act as a control macro. When i run the macro it errors on the paste element of the macro. I like the fact that there is 1 control macro as I do have other macro's to add later which again I can control through the control macro. I need some help with 2 points: 1) Making the "copy region" macro work 2) Linking that macro to the drop down menu in B2. Thanks in advance Mark Code: -------------------- Option Explicit Sub CopyRegion() 'Select and copy correct range Select Case Range("B2").Value Case "Northwest" Sheets("Input Drop").Range("B73:B93").Copy Case "M62corridor" Sheets("Input Drop").Range("B22:B41").Copy Case "M1corridor" Sheets("Input Drop").Range("B6:B20").Copy Case "Northeast" Sheets("Input Drop").Range("B43:B59").Copy Case "NorthernIreland" Sheets("Input Drop").Range("B61:B71").Copy Case "Scotland1" Sheets("Input Drop").Range("B95:B114").Copy Case "Scotland2" Sheets("Input Drop").Range("B116:B131").Copy End Select 'Paste Sheets("Area Summary").Range("B8").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Sort Sheets("Area Summary").Range("B7:M28").Select Application.CutCopyMode = False With ActiveWorkbook.Worksheets("Area Summary").Sort .SortFields.Clear .SortFields.Add Key:=Range("M8:M28"), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .SortFields.Add Key:=Range("E8:E28"), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub NorthWest() ' ' NorthWest Macro ' ' Sheets("Input Drop").Select Range("B73:B93").Select Selection.Copy Sheets("Area Summary").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B7:M28").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub M62corridor() ' ' M62corridor Macro ' ' Sheets("Input Drop").Select ActiveWindow.SmallScroll Down:=-63 Range("B22:B41").Select Selection.Copy Sheets("Area Summary").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub M1corridor() ' ' M1corridor Macro ' ' Sheets("Input Drop").Select Range("B6:B20").Select Selection.Copy Sheets("Area Summary").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B23:B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub Northeast() ' ' Northeast Macro ' ' Range("B8").Select Sheets("Input Drop").Select ActiveWindow.SmallScroll Down:=9 Range("B43:B59").Select Selection.Copy Sheets("Area Summary").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B25:B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub NorthernIreland() ' ' NorthernIreland Macro ' ' Range("B8").Select Sheets("Input Drop").Select Range("B61:B71").Select Selection.Copy Sheets("Area Summary").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B19:B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub Scotland1() ' ' Scotland1 Macro ' ' Range("B8").Select Sheets("Input Drop").Select Range("B95:B114").Select Selection.Copy Sheets("Area Summary").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub -------------------- -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
I suspect you aren't finding a region. There is nothing in the Paste
clipboard so the code is failing when you try to do a paste sopecial. I added some error checking code below. I only including the section of the code that need to be changed. Option Explicit Sub CopyRegion() Dim Found as boolean Dim Region Found = True Region = Range("B2").Value 'Select and copy correct range Select Case Region Case "Northwest" Sheets("Input Drop").Range("B73:B93").Copy Case "M62corridor" Sheets("Input Drop").Range("B22:B41").Copy Case "M1corridor" Sheets("Input Drop").Range("B6:B20").Copy Case "Northeast" Sheets("Input Drop").Range("B43:B59").Copy Case "NorthernIreland" Sheets("Input Drop").Range("B61:B71").Copy Case "Scotland1" Sheets("Input Drop").Range("B95:B114").Copy Case "Scotland2" Sheets("Input Drop").Range("B116:B131").Copy Case Else Found = False End Select if Found = False then msgbox("Could not find region : " & Region & _ vbcrlf & "Exiting Macro") end if' 'Paste Sheets("Area Summary").Range("B8").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False "markrennolds" wrote: Hi All I have various macro's that all work correctly when run independently. I want to run the correct macro for a certain area i.e. North West, when it is selected from a drop down menu in B2. I have had some advice already & have got the following macro "copyregion" which should act as a control macro. When i run the macro it errors on the paste element of the macro. I like the fact that there is 1 control macro as I do have other macro's to add later which again I can control through the control macro. I need some help with 2 points: 1) Making the "copy region" macro work 2) Linking that macro to the drop down menu in B2. Thanks in advance Mark Code: -------------------- Option Explicit Sub CopyRegion() 'Select and copy correct range Select Case Range("B2").Value Case "Northwest" Sheets("Input Drop").Range("B73:B93").Copy Case "M62corridor" Sheets("Input Drop").Range("B22:B41").Copy Case "M1corridor" Sheets("Input Drop").Range("B6:B20").Copy Case "Northeast" Sheets("Input Drop").Range("B43:B59").Copy Case "NorthernIreland" Sheets("Input Drop").Range("B61:B71").Copy Case "Scotland1" Sheets("Input Drop").Range("B95:B114").Copy Case "Scotland2" Sheets("Input Drop").Range("B116:B131").Copy End Select 'Paste Sheets("Area Summary").Range("B8").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Sort Sheets("Area Summary").Range("B7:M28").Select Application.CutCopyMode = False With ActiveWorkbook.Worksheets("Area Summary").Sort .SortFields.Clear .SortFields.Add Key:=Range("M8:M28"), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .SortFields.Add Key:=Range("E8:E28"), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub NorthWest() ' ' NorthWest Macro ' ' Sheets("Input Drop").Select Range("B73:B93").Select Selection.Copy Sheets("Area Summary").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B7:M28").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub M62corridor() ' ' M62corridor Macro ' ' Sheets("Input Drop").Select ActiveWindow.SmallScroll Down:=-63 Range("B22:B41").Select Selection.Copy Sheets("Area Summary").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub M1corridor() ' ' M1corridor Macro ' ' Sheets("Input Drop").Select Range("B6:B20").Select Selection.Copy Sheets("Area Summary").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B23:B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub Northeast() ' ' Northeast Macro ' ' Range("B8").Select Sheets("Input Drop").Select ActiveWindow.SmallScroll Down:=9 Range("B43:B59").Select Selection.Copy Sheets("Area Summary").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B25:B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub NorthernIreland() ' ' NorthernIreland Macro ' ' Range("B8").Select Sheets("Input Drop").Select Range("B61:B71").Select Selection.Copy Sheets("Area Summary").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B19:B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub Sub Scotland1() ' ' Scotland1 Macro ' ' Range("B8").Select Sheets("Input Drop").Select Range("B95:B114").Select Selection.Copy Sheets("Area Summary").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B28").Select Application.CutCopyMode = False Selection.ClearContents Range("B7:M28").Select ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "M8:M28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Area Summary").Sort.SortFields.Add Key:=Range( _ "E8:E28"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Area Summary").Sort .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub -------------------- -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
Hi Joel Thanks for your help. Unfortunately it is still not working. The macro does not run automatically off B2, and when i do run it an error comes up saying "M1Corridor" not found etc. I have pasted the changes straight into both the individual worksheet & the full workbook. Not sure what to do next as I'm new to macro's? Spreadsheet attached if you wouldnt mind having a look? Thanks Mark 185 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=185| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
No need to send the workbook. We found the problem. The error message gave
me the clue. The region name in the pulldown and the region name in the case statement is not exactly the same. Look closely. Try this code below. Option Explicit Sub CopyRegion() Dim Found as boolean Dim Region Found = True Region = Range("B2").Value 'Select and copy correct range Select Case UCASE(Region) Case "NORTHWEST" Sheets("Input Drop").Range("B73:B93").Copy Case "M62CORRIDOR" Sheets("Input Drop").Range("B22:B41").Copy Case "M1CORRIDOR" Sheets("Input Drop").Range("B6:B20").Copy Case "CORRIDOR" Sheets("Input Drop").Range("B43:B59").Copy Case "NORTHERNIRELAND" Sheets("Input Drop").Range("B61:B71").Copy Case "SCOTLAND1" Sheets("Input Drop").Range("B95:B114").Copy Case "SCOTLAND2" Sheets("Input Drop").Range("B116:B131").Copy Case Else Found = False End Select if Found = False then msgbox("Could not find region : " & Region & _ vbcrlf & "Exiting Macro") end if' "markrennolds" wrote: Hi Joel Thanks for your help. Unfortunately it is still not working. The macro does not run automatically off B2, and when i do run it an error comes up saying "M1Corridor" not found etc. I have pasted the changes straight into both the individual worksheet & the full workbook. Not sure what to do next as I'm new to macro's? Spreadsheet attached if you wouldnt mind having a look? Thanks Mark 185 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=185| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
Hi Joel I have tried that & still no luck. I have also copied the text exactly into the macro as it appears on the drop down, and still doesnt find the info. Mark -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
We found the problem. The two strings aren't matching that is why we are
getting to the CASE ELSE. There may be spaces at the beginning or end of the string. try trim(UCASE(Region)) Another trick I use is to get the length of the two strings msgbox(len(Region)) You can upload your code to CodeCage and I will look at it, but the problem is the strings don't match. "markrennolds" wrote: Hi Joel I have tried that & still no luck. I have also copied the text exactly into the macro as it appears on the drop down, and still doesnt find the info. Mark -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
Hi Joel There were spaces before the text so was throwing out the macro. Have sorted that now & macro works fine. However it is not running automatically from the drop down in B2, only when i run it from the macro menu. Any idea's? Thanks Mark 186 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=186| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
I don't like the selection method in the code you have. but don't think tha
is the problem. I'm not usre how you are running the code. I think this statment may be the problem Region = Range("B2").Value There is no sheet reference. You have the same code in VBA sheet "Area Summary" and in Module3. If you are running the code from the worksheet Macro menu you are calling the module3 code. The activesheet will be the sheet that will be used to get REGION. Since your code is switching actrive sheets using selection I'm not sure which sheet is the activesheet. You get my point!!!! I wouldn't put the same code in two places. You can call code in the module sheet from the the "Area Summary" sheet. Not usre why you have any code in the "Area Summary" sheet. "markrennolds" wrote: Hi Joel There were spaces before the text so was throwing out the macro. Have sorted that now & macro works fine. However it is not running automatically from the drop down in B2, only when i run it from the macro menu. Any idea's? Thanks Mark 186 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=186| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
Sorry Joel I understand what is wrong but dont know how to sort it out. I appreciate your help, couldn't have got that far by myself. Can anyone help me out please? I just need to understand how to run the "copyregion" macro by using the dropdown in B2? It works when I run it from the macro menu, so just need the code to look at B2 in "Area Summary" sheet and run the "copyregion" macro. Thanks Mark 187 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=187| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
I'm not usre I understand your problem. The code works for me. If you want
the code to run automatically whenyou change the drop down box put this code in the VBA "Area Summary" sheet. I removed all the other code in this sheet. Didn't need two copies of the code. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Call CopyRegion End If End Sub "markrennolds" wrote: Sorry Joel I understand what is wrong but dont know how to sort it out. I appreciate your help, couldn't have got that far by myself. Can anyone help me out please? I just need to understand how to run the "copyregion" macro by using the dropdown in B2? It works when I run it from the macro menu, so just need the code to look at B2 in "Area Summary" sheet and run the "copyregion" macro. Thanks Mark 187 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=187| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
I'm not usre I understand your problem. The code works for me. If you want
the code to run automatically whenyou change the drop down box put this code in the VBA "Area Summary" sheet. I removed all the other code in this sheet. Didn't need two copies of the code. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Call CopyRegion End If End Sub "markrennolds" wrote: Sorry Joel I understand what is wrong but dont know how to sort it out. I appreciate your help, couldn't have got that far by myself. Can anyone help me out please? I just need to understand how to run the "copyregion" macro by using the dropdown in B2? It works when I run it from the macro menu, so just need the code to look at B2 in "Area Summary" sheet and run the "copyregion" macro. Thanks Mark 187 +-------------------------------------------------------------------+ |Filename: Advanced Estate KPI Tool Area.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=187| +-------------------------------------------------------------------+ -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
Thanks Joel All runs from drop down now. I have tried to put a 'clear contents' rule in before the 'paste' rule as any previous data still in the table is left in there. I copied this from the macro's I had recorded for the individual area's but isn't working in this instance. Sorry to be a nuisance but do you know why? Thanks Mark Code: -------------------- 'Clear Contents Sheets("Area Summary").Range("B8:B28").Select Application.CutCopyMode = False Selection.ClearContents -------------------- -- markrennolds ------------------------------------------------------------------------ markrennolds's Profile: http://www.thecodecage.com/forumz/member.php?userid=543 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118224 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
run multiple macros from drop down list
The clear unselects the copy range. I moved the clear to the beginning of
the sub Option Explicit Sub CopyRegion() Dim Found As Boolean Dim Region Sheets("Area Summary").Range("B8:B28").Select Application.CutCopyMode = False Selection.ClearContents Found = True Region = Range("B2").Value 'Select and copy correct range Select Case (Region) Case "North West" Sheets("Input Drop").Range("B73:B93").Copy Case "M62 Corridor" Sheets("Input Drop").Range("B22:B41").Copy Case "M1 CORRIDOR" Sheets("Input Drop").Range("B6:B20").Copy Case "North East" Sheets("Input Drop").Range("B43:B59").Copy Case "Northern Ireland" Sheets("Input Drop").Range("B61:B71").Copy Case "Scotland1" Sheets("Input Drop").Range("B95:B114").Copy Case "Scotland2" Sheets("Input Drop").Range("B116:B131").Copy Case Else Found = False End Select If Found = False Then MsgBox ("Could not find region : " & Region & _ vbCrLf & "Exiting Macro") End If ' 'Paste Sheets("Area Summary").Range("B8").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Sort Sheets("Area Summary").Range("B7:M28").Select Application.CutCopyMode = False With ActiveWorkbook.Worksheets("Area Summary").Sort .SortFields.Clear .SortFields.Add Key:=Range("M8:M28"), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .SortFields.Add Key:=Range("E8:E28"), SortOn:=xlSortOnValues, _ Order:=xlDescending, DataOption:=xlSortNormal .SetRange Range("B7:M28") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B7").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list and macros? | Excel Discussion (Misc queries) | |||
Yes no drop down list and macros | Excel Discussion (Misc queries) | |||
Drop down list of Macros | Excel Programming | |||
Drop Down List and macros | Excel Programming | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |