How to get require worksheet tab data from excel workbook

Heera

New Member
Joined
Feb 3, 2015
Messages
20
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Try this on a copy of your file


Code:
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
 
Upvote 0
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.
 
Upvote 0
Thanks for the steps, I'm able Run the code, but there is a compile error occurring when sheet name have special characters like "|".
 
Upvote 0
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 -
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top