Omit page with no data when use "show page"
 

I have a VBA to "show page" by user email at a pivot table. However, some of
them are "empty" and without data, how to omit/ delete those worksheets
without data?

Sheets("AllData").Select
ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="User email"

Omit page with no data when use "show page"
 

Is there a particular cell in each worksheet that will always have
content in it?
You could cycle through all the sheets in your workbook and check that
cell. If the cell is empty then you could delete the empty sheet (or
even just hide the empty sheet).

Sub DeleteEmpty()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If IsEmpty(sh.Range("A1")) Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End Sub

Give this code a try and see if it does what you are after.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381176

Omit page with no data when use "show page"
 

The pivot table will create worksheets with "@" as worksheet name.

For each page if it is empty, the grand total at pivot table will be zero.

"bhofsetz" wrote:


Is there a particular cell in each worksheet that will always have
content in it?
You could cycle through all the sheets in your workbook and check that
cell. If the cell is empty then you could delete the empty sheet (or
even just hide the empty sheet).

Sub DeleteEmpty()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If IsEmpty(sh.Range("A1")) Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End Sub

Give this code a try and see if it does what you are after.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381176

Omit page with no data when use "show page"
 

Is the grand total in the same cell on each pivot table created?

If so then you can check to see if this cell is 0 and delete thos
sheets

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38117

Omit page with no data when use "show page"
 

Not the same cell. Is it any object to describe the grand total of pivot
table? So i can check if the grand total equals zero then delete the sheets.

"bhofsetz" wrote:


Is the grand total in the same cell on each pivot table created?

If so then you can check to see if this cell is 0 and delete those
sheets.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381176