How to get require worksheet tab data from excel workbook

[Heera]

Hi Team,

I have data in multiple tabs in one workbook like 40-50 tabs and i need only 8 worksheet tab data which is require for my job and my data is available in different tabs like one lies on 2nd tab and another lies on 12 tab...goes on....

Is there any short cut in macros/formula where i can get sheet tab which i need it and rest need to be deleted ?

Appreciate your help.

Regards,
Heera

 

[DanteAmor]

Try this on a copy of your file

Sub require_worksheet()
    Dim stay As Variant, sh As Worksheet, wRem As Boolean
    
    Application.DisplayAlerts = False
    'write here the name of the sheets you require:
    stay = Array([COLOR=#ff0000]"Sheet1", "sheet2", "sheet3", "sheet4"[/COLOR])
    
    For Each sh In Sheets
        wRem = False
        For j = 0 To UBound(stay)
            If LCase(sh.Name) = LCase(stay(j)) Then
                wRem = True
                Exit For
            End If
        Next
        If wRem = False Then sh.Delete
    Next
    MsgBox "Done    "
End Sub

 

[Heera]

Thanks DanteAmor,

Can you help me step by step on how to add this macro script to my file ?

Regards,
Heera

 

[DanteAmor]

Thanks DanteAmor,

Can you help me step by step on how to add this macro script to my file ?

Regards,
Heera

Of course.

INSERT A MODULE

1. Press Alt-F11 to open the VBA editor.
2. From the menu select Insert > Module.
3. On the sheet that opens (white panel), paste the code previous.
4. Close the editor (press Alt-Q).
5. From Excel, press Alt-F8 to open the macro selector.
6. Select require_worksheet
7. Press Run.

Let me know if you have any doubt.

 

[Heera]

Thanks for the steps, I'm able Run the code, but there is a compile error occurring when sheet name have special characters like "|".

 

[DanteAmor]

Thanks for the steps, I'm able Run the code, but there is a compile error occurring when sheet name have special characters like "|".

These characters
/ \ : * ? " < > |
they are special you should not use them in the names of the sheets, in fact some are not allowed.

I recommend you change the character
|
by other, for example -

 

[Heera]

Please ignore the above reply, I'm getting it now, thank you so much!

 

[DanteAmor]

Please ignore the above reply, I'm getting it now, thank you so much!

I'm glad to help you. Thanks for the feedback.