write code to "click" buttons

Lball

Board Regular
Joined
Oct 4, 2005
Messages
197
I have a simple macro that opens files, copies the page, then pastes in a master doc. After, it closes the file it copied from. It repeats this for 10 different files, opening, copying to master, then closing original. When i run the macro i get those messages asking if i want to save info to clipboard or save changes to original file. Is there a line of code i can put in to choose the "NO" in each of these cases. I'm assuming i'd have to put it in the macro at the corresponding point in the code for when it happens when running.

All help is appreciated!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Just put this at the start of the code.
Code:
Application.DisplayAlerts = False
And this at the end.
Code:
Application.DisplayAlerts = True
It might also help if you post your code - these messages might be able to be avoided rather than just suppressed.
 

Lball

Board Regular
Joined
Oct 4, 2005
Messages
197
Here is a bit of the code. it was recorded. It goe on to open/copy 6 other worksheets as wel
Code:
'Open Ratesheet download
    ChDir "M:\Rate Sheet"
    Workbooks.Open Filename:="M:\Rate Sheet\ROSS RATES NEW DOWNLOAD ONLY.xls", _
        UpdateLinks:=0
    'Open Citi 2nds copy and paste then close
    ChDir "C:\Documents and Settings\lballa\Desktop"
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\lballa\Desktop\Citi 2nd.xls"
    Cells.Select
    Selection.Copy
    Windows("ROSS RATES NEW DOWNLOAD ONLY.xls").Activate
    Sheets("Citi 2nd").Select
    Cells.Select
    ActiveSheet.Paste
    Windows("Citi 2nd.xls").Activate
    ActiveWindow.Close
    'Open Citi copy paste and close
    Workbooks.Open Filename:="C:\Documents and Settings\lballa\Desktop\Citi.xls"
    Cells.Select
    Selection.Copy
    Windows("ROSS RATES NEW DOWNLOAD ONLY.xls").Activate
    Sheets("Citi").Select
    ActiveSheet.Paste
    Windows("Citi.xls").Activate
    ActiveWindow.Close

THe "Master Doc" is password protected. Is there a way to put that in as well?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Which sheet is the master?
Code:
Dim wbMast As Workbook
Dim wbOpen As Workbook
    Set wbMast = Workbooks.Open(Filename:="M:\Rate Sheet\ROSS RATES NEW DOWNLOAD ONLY.xls")
        
    'Open Citi 2nds copy and paste then close

    Set wbOpen = Workbooks.Open(Filename:="C:\Documents and Settings\lballa\Desktop\Citi 2nd.xls")
    wbOpen.ActiveSheet.Cells.Copy wbMast.Sheets("Citi 2nd").Range("A1")
    wbOpen.Close
    
    'Open Citi copy paste and close
    Set wbOpen = Workbooks.Open(Filename:="C:\Documents and Settings\lballa\Desktop\Citi.xls")
    wbOpen.ActiveSheet.Cells.Copy wbMast.Sheets("Citi").Range("A1")
    wbOpen.Close
 

Watch MrExcel Video

Forum statistics

Threads
1,109,350
Messages
5,528,191
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top