Macro to Create Pivot Table

[Lumpy45247]

Please Login or Register  to view this content.

I have created a macro and the above line is adding pivot field to the data field (if I said that correctly) and it then need to do that 12 more times. It works just fine this week, but next week it will not function as the "03" "Min of 03" "04" "Min of 04" will be different week to week. I am wondering if there is a way to select these via a wildcard or another method that I do not need exact name of the field from the data?

[dflak]

You can read this information from a named static range on the spreadsheet. Whether you enter the data in manually to these cells or you calculate them based on the date, I leave to you.

The simplest way to make a named static range is to put the cursor in the cell and then in the name box (upper left) type in the name you want. So if you want to name cell B2 to First_Column, put the cursor in cell B2, then go to the name box (it probably says B2) and type in First_Column. Names have to start with a letter, cannot resolve to an existing cell reference such as END2, can't have spaces and the only punctuation allowed is the underscore.

So say you name B2 to First_Column and B3 to Second_Column and you put 03 and 04 in them respectively (I advise formatting the column as text. Then your code becomes.

Please Login or Register  to view this content.

I don't think you'll have to add quotes around the values for the pivot fields. If you do something like PivotFields(Chr(34) & Col1 & Chr(34)) - where Chr(34) is ASCII for the double quote.