to delete all existing worksheets other than some

  • Hi Experts,
    Could you please tell me a way so that at certain event trigger, I can delete all the other existing worksheets in my workbook except a few (only two)?


    Also, I want to put a validation on certain cells as Integer-Only (of certain length) and Character-Only (also of certain length). There's one cell which should contain xxxx"@"xxxxx.com. How can I keep a check on this "@" symbol?

  • Re: to delete all existing worksheets other than some


    Hi All,


    I've ended up making this code but the problem with this code is that somehow it's deleting one more worksheet (which shouldn't be deleted).


    I'm missing something...

  • Re: to delete all existing worksheets other than some


    Hi,


    Try this:


    [vba]
    Sub delshts()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "t1" or "t2" Then
    ws.Delete
    End If
    Next
    Application.DisplayAlerts = True
    End Sub
    [/vba]

  • Re: to delete all existing worksheets other than some


    This works for me

  • Re: to delete all existing worksheets other than some


    Hi


    1. For xxxx"@"xxxxx.com use this formula in Validation.
    =NOT(ISERROR(FIND("@",C7)))
    2. For character of certain length use Text length option.
    3. For number of certain lenth use Len()< number of digits(Ex: Len(A1)<=10)


    All these use in :DV-Custom-Formula.



    HTH.

  • Re: to delete all existing worksheets other than some


    Hi,


    The problem with deletion of worksheets has been solved!! :)


    I'm still struggling with the other checks which I had mentioned in my first post.


    Also, I'm looking to provide the following features also.
    1. The name (for the workbook and all of the worksheets) shouldn't be editable.
    2. I'm adding several worksheets and huge text into those worksheets using a FOR loop and it's taking considerable time. It feels very ODD to keep looking the screen for that much time. Can I give a Msgbox or a Dialog box to tell the user that "THIS PROCESS WILL TAKE SOME TIME TO FINISH" and that dialog box should dissapear by itself (once the processing is complete)?


    Thx.

  • Re: to delete all existing worksheets other than some


    Check the downloads here for an example to display a wait message
    http://www.ozgrid.com/download/default.htm. I think it is called DisplayWait.zip

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!