Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Macro to automatically close file without the pop-up save ch

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a macro from a spreadsheet (A) that opens up another spreadsheet (B), copies a ranges and pastes it into the A spreadsheet, then closes B.

    The thing is, after that it asks if I want to save the changes to B, and then it says there is a large amount of memory on the clip board and if I want it erased (is fo, choose NO). Is there a statement I can add to my macro to automatically choose No to both pop-up messagebox questions?

    THANKS!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Application.DisplayAlerts = False


    It's never too late to learn something new.

    Ricky

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It works. THANKS! One question though, will the clipboard still be cleared?

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-02 12:18, Cosmos75 wrote:
    It works. THANKS! One question though, will the clipboard still be cleared?
    No ths clipboard will not be cleared so that
    if you have a large amount of data held after
    the save then you will get a msg prompt to
    discard or save it...even with application
    enable events.
    You shold clear the clipboard if you don't
    want this msg prompt.

    Application.cutcopymode = false


    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It works in one workbook and not another! HELP!


    Range("A2").Select
    Range(Selection, Range("A65536").End(xlUp)).Select
    Selection.Copy
    ChDir "C:My Documents"
    Workbooks.Open Filename:= _
    "C:My DocumentsSecond.xls"
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Columns("C:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    'Please make sure that this is the correct spreadsheet
    Windows("First.xls").Activate
    Columns("D:G").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("A1").Select
    Windows("Second.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close


    [ This Message was edited by: Cosmos75 on 2002-04-11 11:17 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi, Cosmos75
    Sub cv()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Range(Cells(2, 1), Cells(65536, 1) _
    .End(xlUp)).Copy
    Workbooks.Open Filename:= _
    "C:My DocumentsSecond.xls"
    Workbooks("Second.xls").Sheets(1). _
    Range("a1").PasteSpecial Paste:=xlValues
    Workbooks("Second.xls").Sheets(1). _
    Columns("C:F").Copy
    Workbooks("First.xls").Sheets(1). _
    Columns("D:G").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Workbooks("Second.xls").Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

    you might want to change a1 to a2 as the destination for the first paste, and also check if you are using sheet 1....


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sutjh,

    Thanks!!

    I never really know which parts of a statement I can take out, like all those extre parts for paste special or pagesetup and things like that. I usually just follow code statement structure that I get from recording a macro.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •