Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Worksheet

    If i am copying a worksheet with the name Test and when copied again it will say Test (2) and again Test (3).

    After 5 copies I have 5 sheets named:
    Test, Test (2), Test (3), Test (4), Test (5)

    How can I tell it to delete all the sheets in the workbook that says Test (#)?

    For i = 1 To Application.SheetsInNewWorkbook

    If ActiveSheet.Name Like "(? (i))" Then
    ActiveWindow.SelectedSheets.Delete
    End If

    Next i

    Please help

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Try using something like this..

    Sub zzzzz()


    application.displayalerts = false
    For Each wk In ThisWorkbook.Worksheets
    If wk.Name Like "Test (*" Then
    wk.Delete
    End If
    Next

    application.displayalerts = true
    End Sub

    zeddy

    PS: I am working on your other posting; will have a nifty solution shortly

  3. #3
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, I tried this code and it is not deleting the page

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Did you cut and paste the code or just retype it?
    For Test (* There is a space after Test and before the left bracket (*

    zeddy

  5. #5
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I cut and pasted the code from the site

  6. #6
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I had to replace the line

    ThisWorkbook.Worksheets

    with

    ActiveWorkbook.Worksheets

    and it is working

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    What version of Excel are you using? If it's 2007 or 2010 you need to change the filetype to .xlsm and enable macros or save the file in a Trusted location.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    2010

Posting Permissions

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