+ Reply to Thread
Results 1 to 13 of 13

VBA to close a sheet and reopen original from desktop

  1. #1
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    VBA to close a sheet and reopen original from desktop

    Is there a VBA code that will allow me to close the sheet i am working on and then reopen the original from my desktop.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,712

    Re: VBA to close a sheet and reopen original from desktop

    Yes, but more detail is needed.

    What do you mean by "original"? Is the one you are working on and want to close a copy of the original? If so, how can you determine the name of the original?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    Re: VBA to close a sheet and reopen original from desktop

    Best i can put it is I open the original but then it automatically is remanem according to info in a certain cell. so what i want to do is when i hit my command button i want it to close the current one i am working on which has been renamed and then open the original off the desktop

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA to close a sheet and reopen original from desktop

    Something like this maybe?

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA to close a sheet and reopen original from desktop

    BTW your Command Button and code will have too reside in another workbook and not the activeworkbook.

  6. #6
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    Re: VBA to close a sheet and reopen original from desktop

    so there is no way to do it using the command button in my current workbook?

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA to close a sheet and reopen original from desktop

    If your current workbook is the one housing the macro once you close it automatically, the macro will stop. You can't reopen it from the same macro.
    Last edited by JOHN H. DAVIS; 10-22-2013 at 12:55 PM.

  8. #8
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    Re: VBA to close a sheet and reopen original from desktop

    ok then can i just set the macro to just close it, and i can have them open the new one manually

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,712

    Re: VBA to close a sheet and reopen original from desktop

    I have not prototyped this but surely the code can open the other workbook first, then close itself.

  10. #10
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    Re: VBA to close a sheet and reopen original from desktop

    how do i write it.

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,712

    Re: VBA to close a sheet and reopen original from desktop

    I've reviewed the thread again and think this solution is a bit cleaner: Save a copy of the current workbook with a new name, and leave the current workbook open. The workbook with the new name is never opened.
    Please Login or Register  to view this content.
    You would have to modify it to give the desired filename using the content of the desired cell.
    Last edited by 6StringJazzer; 10-23-2013 at 12:53 PM. Reason: added ThisWorkbook qualifier to avoid any ambiguity

  12. #12
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    Re: VBA to close a sheet and reopen original from desktop

    Batch choochoo.xlsso i put this in the below workbook, but it is not saving it to the drive and file written in the macro

  13. #13
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,712

    Re: VBA to close a sheet and reopen original from desktop

    It works. You didn't give any detail so I couldn't write code to match your situation, and you didn't modify the code. You have the following code:
    Please Login or Register  to view this content.
    Modify it to look like this.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. (Function) Have to open then close then reopen to work on wookbook
    By amsanborn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2012, 04:14 PM
  2. [SOLVED] macro close without saving reopen
    By g48dd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2010, 10:27 PM
  3. save,close then reopen original
    By stevesunfold in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2008, 02:32 PM
  4. Replies: 1
    Last Post: 07-16-2006, 05:44 PM
  5. [SOLVED] VBA Code dosent run after close & reopen
    By Jagdip Singh Ajimal in forum Excel General
    Replies: 2
    Last Post: 04-25-2005, 09:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1