Posted to microsoft.public.excel.misc
|
|
Pop-up when running a macro
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
--
Regards
Roger Govier
"tom" wrote in message
...
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" wrote:
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
--
Regards
Roger Govier
"tom" wrote in message
...
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
"Anne Troy" wrote:
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
****************************
"tom" wrote in message
...
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
|