+ Reply to Thread
Results 1 to 11 of 11

Delete sheets by name

  1. #1
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Delete sheets by name

    I have a list of worksheets and want to delete the last X worksheets as listed in column B in worksheet(1). Also, I get an error the first time I run this code but not the second time.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Delete sheets by name

    What's the error message you get and how many sheets in total do you have?

    There is nothing in the delete loop that takes account of any names listed on the left hand sheet tab. And although the ClearContents instruction is commented out it doesn't make sense.
    Columns ("A") or Columns(1) would.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Delete sheets by name

    Please Login or Register  to view this content.
    You need to delete from right to left with that indexing. Think about it.

    Either that, or

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Delete sheets by name

    Quote Originally Posted by Richard Buttrey View Post
    What's the error message you get and how many sheets in total do you have?

    There is nothing in the delete loop that takes account of any names listed on the left hand sheet tab. And although the ClearContents instruction is commented out it doesn't make sense.
    Columns ("A") or Columns(1) would.
    I get a "Run-time error'9': Subscript out of range" and it stops at line "Sheets(N + i).Delete"

    The number of sheets in total and to delete will vary. I currently have another macro that will list out all the sheets in column A. From there I'd like to skip the first N sheet names and delete any sheets after that along with the list of sheet names in column A.

  5. #5
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Delete sheets by name

    Ok, here is the last line I need to figure out why it isn't working. Any assistance is appreciated.
    Please Login or Register  to view this content.
    Last edited by sdgenxr; 12-03-2015 at 04:13 PM.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Delete sheets by name

    What happens?

    What are the worsheet names, exactly?

  7. #7
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Delete sheets by name

    It deletes some of the worksheets then errors out with a runtime error. The worksheets are named "FY14" etc.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Delete sheets by name

    Worksheets and other collections renumber as items are deleted, so you need to loop backwards:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Delete sheets by name

    I see. Thank you for the assistance!

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

    Re: Delete sheets by name

    Maybe:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Delete sheets by name

    Better .

+ 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. Replies: 1
    Last Post: 10-04-2013, 04:21 AM
  2. Code to delete sheets and rename sheets in a protected workbook?
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-07-2011, 03:02 AM
  3. Delete Rows by Cell Value - Delete sheets w/out prompt
    By Jimmydageek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2010, 02:03 PM
  4. [SOLVED] Macro to delete sheets and saves remaining file does not properly delete module
    By pherrero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2005, 08:12 PM
  5. [SOLVED] Re: Macro to delete sheets and saves remaining file does not properly delete module
    By bhawane in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-21-2005, 01:05 PM
  6. [SOLVED] Re: Macro to delete sheets and saves remaining file does not properly delete module
    By pherrero in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-21-2005, 01:05 PM
  7. Dialog Box to Select Sheets to Make Visible and Delete the Remaining Sheets
    By TEAM in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-23-2005, 04:59 PM

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