Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Pop-up when running a macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
tom tom is offline
external usenet poster
  
Posts: 570
Default Pop-up when running a macro
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 221
Default Pop-up when running a macro
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
tom tom is offline
external usenet poster
  
Posts: 570
Default Pop-up when running a macro
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,886
Default Pop-up when running a macro
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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
tom tom is offline
external usenet poster
  
Posts: 570
Default Pop-up when running a macro

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






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,886
Default 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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Event Macro running another macro inside K1KKKA Excel Discussion (Misc queries) 1 December 20th 06 08:21 PM
disable user running macro from Tools Macro Steve Simons Excel Discussion (Misc queries) 4 September 28th 06 06:28 AM
Macro not running mowen Excel Discussion (Misc queries) 4 September 12th 05 06:33 PM
Running a VBA Macro Jeff Excel Discussion (Misc queries) 2 March 15th 05 02:13 PM
Running VBA Macro Jeff Excel Discussion (Misc queries) 1 February 15th 05 01:12 PM


All times are GMT +1. The time now is 03:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement