Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use Page Break Preview without seeing "PAGE" in sheet? | Excel Discussion (Misc queries) | |||
Are the "page setup" layout settings overwritten by page preview adjustments ? | Excel Discussion (Misc queries) | |||
How do I lighten the word "Page" (and number) in Page Break View? | Excel Discussion (Misc queries) | |||
How do you get "Show Page" functionality in MSExcel PivotTables | Excel Discussion (Misc queries) | |||
"Can't shift excess data off page"??? | Excel Programming |