Thread: run multiple macros from drop down list
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
markrennolds markrennolds is offline
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote