Pop-up when running a macro

[Guest]

While I am running a Macro... I would like for the end user to see (Sheet 3 -
which is currenlty hidden) for the whole duration of the macro. After the
macro is completely done running I would like for (sheet 3) to hide again.

TFTH,
Tom

 

[Anne Troy]

First line of code after you declare your variables:
Sheets("Sheet3").Visible = True

Last line before you end sub:
Sheets("Sheet3").Visible = False
And don't use a .ScreenUpdating = False.

****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Guest]

Anne,
Thanks but I am not sure exactly where to put your code can you assist?

' rerunprocess Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Sheets("Satisfied Back Orders").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Back Order Roll-up").Select
ActiveWindow.SmallScroll ToRight:=-6
Calculate
Range("I4").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Columns("J:L").Select
Selection.Copy
Columns("M:O").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("P4:S4").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("P4:S146")
Range("P4:S146").Select
Calculate
Sheets("Container Roll-up").Select
Range("X4").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Columns("Z:AA").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=5
Columns("AG:AH").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("AI4:AM4").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AI4:AM329")
Range("AI4:AM329").Select
Calculate
ActiveSheet.PivotTables("PivotTable9").PivotSelect "NYKU5497692", _
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
ActiveWindow.SmallScroll ToRight:=5
Range("AQ6:AS6").Select
Selection.AutoFill Destination:=Range("AQ6:AS229")
Range("AQ6:AS229").Select
Calculate
Range("AO6").Select
End Sub

Thanks again,
Tom

 

[Roger Govier]

Hi Tom

I have inserted the relevant lines at the top and bottom of your code.
I have amended your code slightly to speed it up. You seldom need to
Select items before copying them (though this is what the macro
recorder does).

Sheets("Sheet3").Visible = True
Application.ScreenUpdating = False
Sheets("Satisfied Back Orders").Actvate
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Back Order Roll-up").Activate
Calculate
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Columns("J:L").Copy
Columns("M:O").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Range("P4:S4").Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("P4:S146")
Calculate
Sheets("Container Roll-up").Activate
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Columns("Z:AA").Copy
Columns("AG:AH").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AI4:AM4").Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AI4:AM329")
Calculate
ActiveSheet.PivotTables("PivotTable9").PivotSelect "NYKU5497692", _
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
Range("AQ6:AS6").Copy
Selection.AutoFill Destination:=Range("AQ6:AS229")
Calculate
Range("AO6").Activate
Sheets("Sheet3").Visible = False
Application.ScreenUpdating = True

 

[Guest]

Hi Roger,
Thanks for your assistance... The code looks great and I understand about
trying to make it run faster. I Inserted the code you updated and it faulted
at

Selection.AutoFill Destination:=Range("P4:S146")
Calculate and
Selection.AutoFill Destination:=Range("AI4:AM329")
Calculate and
Selection.AutoFill Destination:=Range("AQ6:AS229")
Calculate

any ideas?

 

[Roger Govier]

Hi Tom

Then perhaps re-inserting prior to each calculate, the selection of the
range as in your original code. But use activate instead of select.
Range("P4:S146").Activate