+ Reply to Thread
Results 1 to 3 of 3

Delete worksheets based on a list

Hybrid View

  1. #1
    Registered User
    Join Date
    11-13-2015
    Location
    Indiana, U.S.
    MS-Off Ver
    Office 2013
    Posts
    5

    Delete worksheets based on a list

    Hi everyone, I am having trouble with my VBA code in one particular instance. I have this code that will delete any worksheet that is not specified on a list in the first workbook, and everything works fine except for when the list is empty. When the list is empty, I need all of the worksheets to be deleted except for the one with the list.

    Here is the code:

    Dim bottomA As Long
    Dim c2 As Range
    Dim ws2 As Worksheet

    'Set Range

    bottomA = Range("A" & Rows.Count).End(xlUp).Row

    'Loop through each value in range and create worksheet
    For Each c2 In Range("A2:A" & bottomA)
    Set ws2 = Nothing
    On Error Resume Next
    Set ws2 = Worksheets(c2.Value)
    On Error GoTo 0
    'set the name of the worksheet

    If ws2 Is Nothing Then
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c2.Value 'this is where the error message pops up
    End If
    Next c2

    When it runs when there are no items on the list, it deletes all of the worksheets and keeps the one with the list, but it creates a new one and an error message pops up. Any help would be greatly appreciated!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,058

    Re: Delete worksheets based on a list

    When you add a worksheet, it becomes the active worksheet. So, at that point, c2.Value will have nothing in it because it relates to the currently active sheet, the one you just created. Try fully qualifying c2.Value with the original sheet name.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,058

    Re: Delete worksheets based on a list

    Oh, and I should have said ... Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

+ 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: 3
    Last Post: 11-13-2015, 03:31 PM
  2. [SOLVED] Delete Worksheets Based on Name from InputBox Criteria
    By d247 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2014, 01:45 AM
  3. Delete Worksheets not on list or in named range?
    By Unpackedrope in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-09-2013, 04:12 PM
  4. Replies: 2
    Last Post: 05-06-2013, 08:53 AM
  5. Delete Row based on cell value through multiple worksheets
    By jgrant1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-08-2012, 03:19 PM
  6. Delete Worksheets Based On Name
    By Fermented Reptile in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2012, 11:02 AM
  7. Do while loop to delete worksheets if worksheets are not in the specified list
    By kchm_2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2011, 03:02 PM

Tags for this Thread

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