Excel 2007 Pivot Table syntax - select all

[LisaBee]

My macro adds data every week, and refreshes the pivot tables accordingly. I want to display all columns in the pivot table EXCEPT those values which are blank or #N/A. The added columns may have different names, so I can't request they be made visible by name (unless wildcards will work -- all columns start with the word "week"), so I'd like to just select all to ensure any new columns are picked up, then remove the two that I know I don't need. But the "(Select All)".Visible = True seems to be invalid. What is the correct syntax for selecting all columns?

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Query Establish Week")
.PivotItems("(Select All)").Visible = True '<<?
.PivotItems("(blank)").Visible = False
.PivotItems("#N/A").Visible = False
End With

Thanks very much in advance!

Lisa

 

[SkipVought]

Hi,

all columns start with the word "week"),

Sorry. You are building your table in a way that is renders many of Exce's features useless or very difficult to use as designed.

Well designed tables never have colums added as data is added. This is a non-normalized design and is not considered a best and accepted practice.

Your Table needs some kind of REAL DATE column, for the "week" that you add each week, in addition to a column for the numeric value.

Then you can pivot from this proper table, that increases in ROWS but not COLUMNS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Lisa, building off of Skip's comments...

Can you show us the basic layout of your table?
All we really need to see is the type of data that's going into each column.
for example:

ID, Date, Name, Product, Amount, ...

And also what structure the data comes in as.

It sounds like each "column" you get is for a specific date, and that each "row" in your column has all the appropriate data. In which case, Skip is absolutely correct, you need to transpose your data layout.

Your macro can be altered to add rows as opposed to columns, and I guarantee that will make your life tons easier.

 

[PHV]

remove the two that I know I don't need
How do you know which ones ?

Skip, the OP talk about a PivotTable and thus we don't expect a mormalized result ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

PHV,

I was assuming that the SOURCE table was having added columns.

Perhaps I misunderstood th OP. After reding again, you may be correct that the OP is referring the the PT results and not the source.

Lisa, If this is true, you may want to limit your source data table to ONLY the weeks that are applicable. You can use MS Query to generate a subset of your table and then use THAT table as the source for your PT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LisaBee]

I agree that this pivot table is probably not the best way to go with what is being accomplished, but it was set up by an outside agency, and we're stuck with it. The original data which the pivot table draws from is on Tab 1, and it contains a column which identifies the week that particular line of data originated. Each cell in this column will identify which week the data originated (from the original week (i.e.,"week starting 2/1/10", to the most recent week's data "week starting 4/26/10") always starting on a Monday. Because the data is dynamic, it's necessary to repull all weeks rather than just add the most current week's data. No columns added on the original data, just the new value of the date of the most current week).

The pivot table indicates
Wk 2/1 Wk 2/8 Wk 2/15 . . .
Location A
Location B
Location C
Location D

When the pivot table is refreshed, I want it to pick up the additional week (and all previous weeks) without having to actually specify the most current week ("week starting 4/19/10"). There are only 2 values that will appear in the original data which shouldn't be included -- blanks and values of #N/A. Since I know how to omit the blanks and #N/A (as shown in the example above), I was hoping there was a way to select all values to pick up the added week value in the original data without having to actually specify "week starting 4/26/10".

Is there perhaps some syntax which would allow a wildcard so that all data beginning with "week starting" would automatically be included and all others (blanks & #N/A) omitted from the selection?


Thanks again for all input!

 

[SkipVought]

Turn on your macro recorder and record manually selecting the COLUMN values you want.

Post back with your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LisaBee]

Because it keeps the other fields as originally selected, and all I'm doing is adding the newest week, this is all that is recorded:

With ActiveSheet.PivotTables("PivotTable9").PivotFields("Query Establish Week")
.PivotItems("Week Starting April 26").Visible = True
End With

This will eventually include the remaining weeks through December 2010 -- perhaps further.

 

[SkipVought]

I guess I'm confused.

If you are just adding rows to your source data, then ALL you need to is either adjust the source data range manually or DYNAMICALLY (faq68-1331), and REFRESH your PT and the new column should autonatically be displayed.You sould really, not have to make visible. Then only thing you might want to do is make some NOT Visible.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LisaBee]

The data range is currently set for A:X -- (I know that's not good, but it's not my creation). I've used your suggestion before in 2003 (naming the data range), but I'm guessing because 2007 allows the user to specify each label, it doesn't pick up the new date since the default filter doesn't already have it selected.

 

[SkipVought]

It wiil when you REFRESH the PT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LisaBee]

When I refresh the PT, the original weeks which were selected are still selected, the two values which were not selected ( "(blank)" and "#N/A") are still not selected, and the new value, is not selected.

In order for the pivot table to add the additional column, I need to physically select the filter I want added for it to appear. BUT . . . I did just try this, and it works perfectly . . .

ActiveSheet.PivotTables("PivotTable6").PivotFields("Query Establish Week"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields("Query Establish Week"). _
PivotFilters.Add Type:=xlCaptionBeginsWith, Value1:="Week"

Everyone -- thanks very much for your input!

Lisa

 

[SkipVought]

Wonderful!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}