vba courses london - Input box for selecting a worksheet

Carlos,

I have a question regarding creating an input box to select a particular worksheet (tab).

Each month I move and copy several worsheets to a new worbook and save them as values only so that I can distribute them as reports. All bar one of the worksheets are the same for each month. For the particular worksheet that varies from month to month I want to be able to create an input box so that I can select the worksheet relevant to the particular month. Below I have included the code that I have recorded. You will notice that in the macro the worksheet that I have selected for this month is "M1". Next month I want this to be "M2" and then the month after "M3" and so forth. Do I have to declare "M1" to be a variant? Can you please help me with code?

Thanks Simon Gleeson Ph: 02077667200

Sub Create_Month_End()
'
' Create_ME Macro
' Simon Gleeson
'

Outcome = MsgBox("Are you sure you want to create the Month End Report?", vbYesNo, "Month End Report")
If (Outcome = 6) Then
Sheets(Array("Cover Page", "Commentary", "M1")).Select
Sheets("Cover Page").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("Cover Page", "Commentary", "M1", "YTD", "Global Analytics")).Select
Sheets("Global Analytics").Activate
Sheets(Array("Cover Page", "Commentary", "M1", "YTD", "Global Analytics")).Copy
ActiveSheet.Shapes("Button 1").Select
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.ClearContents
Sheets("Commentary").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.ClearContents
Sheets("M1").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 21
ActiveWindow.SmallScroll Down:=-216
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
Columns("A:A").Select
Selection.ClearContents
Rows("4:7").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("YTD").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").Select
Selection.ClearContents
Range("A2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Rows("4:7").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Global Analytics").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").Select
Selection.ClearContents
Range("A1").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Rows("18:21").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover Page").Select
ActiveWorkbook.SaveAs Filename:= _
"S:\London Office\All Files\Finance\Management Accounts\SG Management Accounts.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Windows("Group GLOBAL Analytics 2007 Template 19 Feb 2007.xls").Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cover Page").Select
Range("A1").Select
End If
End Sub