Delete Sheets Created by Pivot Table

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
548
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
548
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,140,921
Messages
5,703,174
Members
421,279
Latest member
emzy

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
Top