Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    close all worksheets (office xp 2000)

    though i can not take credit for this little gem because it dose work -------- my problem i possibly others who might want to use it ( and this is where the help is required) it will only close worksheets i.e. sheet2, sheet3, sheet4......... sheet20. I like most people know name each sheet for simplification.
    how can this programe be altered to delete named sheets, and can this be achieved when exiting the workbook, in my case the format of the worksheet is updated by a link to the data, if i donot delete the 20 sheets before closing the workbook , it takes a lonkgtime to reopen the work book .
    Sub Sh_delete()
    Dim I As Integer
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
    If Worksheets(I).Name <> "Sheet1" Then Worksheets(I).Delete
    Next I
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub
    any one any ideas

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    The code already deletes sheets without regard to name, except that it looks for a sheet named "Sheet1" that it will not delete. If you want the user to be able to change the name of Sheet1 to something else and still use the code, then you will have to give us some other way to recognize the sheet that is not to be deleted. For example, can something be placed in a cell on the worksheet that lets the code know that this is the sheet that is not to be deleted? Could the code assume that the sheet is always the first sheet in the workbook? Can the code look for a particular codename (the name that is not in parenthesis in project explorer), for example "Sheet1"? If you are not doing anything that would cause the codename to change, then the following might work for you:

    <pre>Option Explicit

    Sub Sh_delete()
    Dim I As Integer
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
    If Worksheets(I).CodeName <> "Sheet1" Then Worksheets(I).Delete
    Next I
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    thank you for your input, every thing works if the "sheet1" is the first sheet where as in my case it is the last sheet. when i return to work on tuesday i will try this out by moving sheet 1 to the first position.
    i have tried re-naming the sheet "com056" but this dose not work it all ways deletes all sheets except the sheet in position 1, have you any suggestions on how to leave just the named sheet on exit.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: close all worksheets (office xp 2000)

    Change the "sheet1" in the line"

    If Worksheets(I).CodeName <> "com056" Then Worksheets(I).Delete

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    I'm not sure I understand. Did you change the sheet name (the one in parenthesis in project explorer, and the one that can be changed using the Format/Sheet/Rename), or did you change the Codename (the one not in parenthesis that you have to change in project explorer properties window)? My code should delete all sheets except one with Codename Sheet1. If there is not a sheet with Codename Sheet1, then it will delete all but the first sheet in the workbook since Excel will not allow all sheets to be deleted. Can you upload a copy of the workbook with all data deleted from the worksheets? It sounds like you don't have the correct codename in the code.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    thank you for your input, unfortunately i am off work until wednesday. i will take a copy of the workbook with all data deleted an post a copy.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    Your macro is running through all the sheets by index number (The I in "<font face="Georgia">Worksheets(I).Name</font face=georgia>") without regard to sheet names. It's easy to be confused here, since Excel uses Sheetn as the default sheet name.

    The real business is in the IF THEN block. That's where you can check for actual sheet names (<font face="Georgia">Worksheets(i).Name</font face=georgia>) and delete the ones you don't need. Remember, you can use <font face="Georgia">Left(string, length)</font face=georgia> to deal with sheets that begin with the same thing (as Payroll08, Payroll15, Payroll22, etc.). Other text functions could work for you as well.

    Hint: Depending on what you're doing, you might use <font face="Georgia">Select Case</font face=georgia> instead of <font face="Georgia">If Then</font face=georgia> to deal with the sheets if you have a variety of situations

    Errol

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    i have repositione "sheet1" to be the first sheet and get the following error
    Run time error "1004"
    A workbook must contain at least one visible worksheet
    close END and all worksheets are removed except "sheet1"
    why the error? can that be written out to overlook the fact that there is only "sheet1" left

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: close all worksheets (office xp 2000)

    "Sheet1" <> "sheet1"
    Note this is case sensitive

    Change your code to match the sheet name ("Sheet1") or use:

    <pre>If ucase(Worksheets(I).CodeName) <> "SHEET1" Then Worksheets(I).Delete</pre>


    Steve

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close all worksheets (office xp 2000)

    "Sheet1" <> "sheet1"
    Note this is case sensitive
    How stupid can one be, i fell for the 4 card trick, it's simple change the "s" in "sheet1" to "S", works a treat

    thank you for all your help

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: close all worksheets (office xp 2000)

    It is not really "stupid". In many instances they can be used interchangeably:
    eg you can enter:
    Worksheets("sheet1").Select
    or
    Worksheets("Sheet1").Select

    and excel will find them.

    It is VB's "=" which compares letter by letter which is the issue. If you are like me, you will be bit several more times with it and you will still not remember it and fail again later <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

Posting Permissions

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