Delete Sheets Created by Pivot Table

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
590
Office Version
  1. 2019
Platform
  1. Windows
Hello-

I have a workbook that I created to format and rearrange some data, it then creates a Pivot Table. That works great.

I use this code in another button to reset the workbook:

VBA Code:
Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
        
    Sheets("Load Data").Delete
    Sheets("Pivot").Delete
    
    Sheets.Add(After:=Sheets("Index")).Name = "Load Data"
    Sheets.Add(After:=Sheets("Load Data")).Name = "Pivot"
    
    Sheets("Index").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

However, I found that the users will click on the grand totals inside the pivot table to look at tabled data, when that happens, the sheet will be named something like Sheet7, Sheet8, etc. How can I add to my code to grab any Worksheet that has the name Sheet in it to have it deleted when the workbook is reset?

thank you,
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
How about
VBA Code:
   Dim Ws As Worksheet
   Application.DisplayAlerts = False
   For Each Ws In Worksheets
      If Ws.Name Like "Sheet*" Then Ws.Delete
   Next Ws
   Application.DisplayAlerts = True
 
Upvote 0
Solution
How about
VBA Code:
   Dim Ws As Worksheet
   Application.DisplayAlerts = False
   For Each Ws In Worksheets
      If Ws.Name Like "Sheet*" Then Ws.Delete
   Next Ws
   Application.DisplayAlerts = True
perfect, mark as solution
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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