+ Reply to Thread
Results 1 to 5 of 5

macro close without saving reopen

  1. #1
    g48dd
    Guest

    macro close without saving reopen

    Using excel 2003, I have a ss that uses filters and sorting. I can get to the data I want but must reopen the workbook if I want to start over and extract different information. I tried installing this VBA:

    HTML Code: 
    Sub CloseMe() 
        Application.OnTime Now + TimeValue("00:00:10"), "OpenMe" 
         ThisWorkbook.Close False 
    End Sub 
     
    Sub OpenMe() 
         MsgBox "I'm Back!" 
    End Sub

    This is my first time at inserting VBA. Usually I just use keystroke macros. I have set my Excel VB Book options to: Option Explicit double clicked the sheet I was using and under general inserted the code, closed Ctrl+Q, saved workbook. When I reopened and tried to use it all I get is I'm Back! nothing else happens. One time it actually ran and reopened but it appeared that the workbook was saved before closing because it was still sorted. What I need is it to completely unsorted as it is when I first open it so I can start over.

    Thank you,
    Ken
    Last edited by g48dd; 01-09-2010 at 10:23 PM. Reason: Forgot to wrap code in HTML Tags

  2. #2
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: macro close without saving reopen

    Instead Id suggest adding a column that is just numbered (1 - 1000 etc). When you need to 'get different data' then just sort it back to the original order and undo the filter option.

    You could easily create a macro to sort / undo filters, and it would not require closing the workbook without saving / reopening it.

  3. #3
    g48dd
    Guest

    Re: macro close without saving reopen

    Hi Thank you, I do have a column that can be used and I have a macro assigned to a button that will do that, however the problem is that the SS also has Auto Filter at top of sheet so that it is possible run a sort then filter to separate, then usually I run one macro to sort. I can undo things but explaining to someone else how to do, not always successful, if I only had to explain or teach once or twice I could do that but many people use this SS to get information they need and the workbook needs to be set back to beginning. If someone saves before they close, the next person is screwed, I get a call. So the easiest way I could think of was to put a big button with a note on the sheet PLEASE CLEAR SHEET before closing. Personally I would like to have it to save steps each time I use it also. It is the running of the Auto Filters that is the problem really all I need is a macro that sets all the auto filters to ALL and the sorts column A ascending. If I had that I would not have to close and reopen without save.

    Thanks,
    Ken[/SIZE]
    Last edited by shg; 01-10-2010 at 12:02 AM.

  4. #4
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: macro close without saving reopen

    You can record that with the Macro Recorder and modify as needed.

  5. #5
    g48dd
    Guest

    Re: macro close without saving reopen

    Marzuk you had the right idea, all I had to do was record a simple keystroke macro where I clicked on each drop down arrow of the auto filter and set each one to all, then click my reset macro that I already had in place, stop macro. It runs just fine resets everything and then runs the reset sort button I had already created.

    Ken

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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