During the VBA course with Carlos he indicated that you could run a macro and prevent the screen from flickering (i.e while it was calculating). Can you please let me know how to do this?

Running VBA code may cause the screen to flicker as the monitor is the slowest part of the program and cannot keep up with the very fast changes taking place.

To switch off the screen until the program is run enter the following code line at the beginning of the procedure:

Application.ScreenUpdating = False

The screen comes on automatically on completion of the program. If you require screen updating to resume before the end type


Application.ScreenUpdating = True

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

Hi Simon

Sorry for the delay.

Yes. If you are changing the sheet monthly you need to replace the "M1" value in the code with a variable (As next month it will be "M2")

eg. dim MonthName as String

Then create either an input box or a form to that asks the user to enter the name of the new monthly sheets as required. This entry gets saved in MonthName.

This inputbox/Form is activated if Outcome=6