Add code in macro to say NO to file update,file save and save clipboard contents

S

singlgl1

Board Regular
Joined
Jan 13, 2007
Messages
127
During the Macro with the following code, as it opens files associated it prompts
"do you want to update",
When it closes files during macro, it asks if I want to save,
and it also prompts me whether I want to save contents of clipboard to paste later. I'd like to add into the code to automatically select "NO" to all of the above prompts.

Here's the current code:


Sub ImportEvents()
'
' ImportEvents Macro
'

'
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-2-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-1-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-2-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-3-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-2-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-3-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-4-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-3-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-4-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-5-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-4-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-5-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-6-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-5-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-6-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-7-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-6-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-7-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-8-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-7-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-8-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-9-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-8-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-9-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-10-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-9-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-10-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-11-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-10-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-11-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-12-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-11-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-12-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-13-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-12-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-13-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-14-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-13-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-14-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-15-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-14-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-15-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-16-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-15-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-16-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-17-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-16-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-17-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-18-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-17-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-18-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-19-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-18-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-19-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-20-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-19-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-20-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-21-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-20-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-21-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-22-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-21-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-22-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-23-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-22-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-23-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-24-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-23-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-24-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-25-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-24-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-25-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-26-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-25-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-26-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-27-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-26-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-27-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-28-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-27-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-28-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-29-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-28-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-29-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jun\6-30-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-29-11").Select
Cells.Select
ActiveSheet.Paste
Windows("6-30-11.xls").Activate
ActiveWindow.Close
'Next Day
Workbooks.Open Filename:="H:\Morning Reports 2011\Jul\7-1-11.xls", _
UpdateLinks:=0
Sheets("Flare & Vent Tracking").Select
Cells.Select
Selection.Copy
Windows("Plant Flare & Vent Consolidated 06-11.xlsm").Activate
Sheets("6-30-11").Select
Cells.Select
ActiveSheet.Paste
Windows("7-1-11.xls").Activate
ActiveWindow.Close
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Try adding
Application.DisplayAlerts=False to the beginning and
Application.DisplayAlerts=True to the end
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Date problem
Replies
2
Views
404
vmjan02
V
T
  • Question Question
VBA Macro running, but not saving to Sharepoint
Replies
3
Views
771
Fluff
Fluff
L
  • Question Question
VBA Modify code to say "If" file exists
Replies
3
Views
472
Joe4
Joe4
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
664
ghrek
G
B
  • Question Question
VBA to extract data from multiple folders and add to a master workbook plus identifying tags
Replies
2
Views
1K
John_w
J

Forum statistics

Threads
1,224,609
Messages
6,179,873
Members
452,949
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top