Creating a Pivot Table based off an ever changing range...

instead of using VBA use dynamic named ranges with offset and Counta.

Anytime I think I MIGHT update a file or the data, I create a named range called PivotDATA. I make use the following to make a dynamic named range with that.

I select the data and hit INSERT Pivot, and instead of leaving the hardcoded range, I type in PivotDATA.


Give me a second to make an example.

So the dynamic range works like this

Please Login or Register  to view this content.

It starts at cell A1
Offsets 0 rows and 0 columns
counts how many cells are used in column A then goes down that many rows
counts how many cells are used in row 1 then goes to the right that number of columns

SO if you want, you can FIX the number of columns with a static number or you can use the COUNTA method.
Also, be aware if you type something in row 1 outside of the data, it will count it and add a row, and the same thing goes for adding anything in column A

Try adding a few more full columns of headers and data
Try adding more rows of data
The PivotDATA named range will capture that dynamically.

To test it, type the name PivotData into the named range area to the left of the formula bar.


DynamicNamedRange.xlsx


Obviously if you WANT to use VBA, you can easily do that as well.

Did you even try my method?

Alright, let me know if you have any questions or if you still prefer to use a named range and/or VBA.

Originally Posted by Joven76

Hey Mike,

I'm not sure this will work for what I'm trying to do. Let me try to explain. We have a list of inventory items that are currently on back order. I've set up the spreadsheet using VBA so that it only shows orders needing inventory 45 days into the future, so as you can see, it will change from day to day.

So there is one inventory line item with their quantity for each order. Therefore there can be and are multiple line items of the same inventory part number and quantity. The pivot table shows me these part numbers and the total quantity of all the line items.

This information will then be drawn into another spreadsheet using a VBA macro, so I need to do all of this within VBA.

Any suggestions?

Explain why it wont work.
You already said you have more or less rows correct? This captures those.

Do yourself a favor and post a sample data set.

Here is a sample set of data:

Invt ID Description PromDate BO qty
660422 Customization kit: 9/26/2014 1
660423 Wristband dark grey 9/26/2014 1
110-3614B ZONE INTERFACE UNIT PCB (ZIU) 9/10/2014 1
110-3628C TELCO PCB - ONE FOR EACH ANALOG LINE 10/1/2014 2
18A424 CORRIDOR/DOOR LIGHT 4 LAMP, 12V - BEIGE 9/25/2014 3
200-1073 AIR PAD/ CALL CORD SINGLE - 10FT 9/15/2014 30
200-1171 CALL CORD SINGLE PUSH BUTTON - 6FT 6/15/2014 6
211-1297A COVER CORR + ZONE LT 9/18/2014 12
438-537C CABLE KIT (8') CONNECTS TO 37-PIN PC6K 9/5/2014 38
438-537C CABLE KIT (8') CONNECTS TO 37-PIN PC6K 9/19/2014 14
439-0003-001 LICENSE - POCKET PAGE BRIDGE 6/15/2014 1
4A2481B SINGLE PATIENT STATION - 3 GANG - GRAY 8/29/2014 4
4A2481B SINGLE PATIENT STATION - 3 GANG - GRAY 9/5/2014 3
4A2481B SINGLE PATIENT STATION - 3 GANG - GRAY 9/23/2014 6
4A2481B SINGLE PATIENT STATION - 3 GANG - GRAY 9/25/2014 3
4A3092A STAFF/DUTY STATION - INTERCOM AND TONE 9/25/2014 4
9A2220 STAFF EMERGENCY STATION - GREEN PUSH BUT 9/25/2014 3
9A3006 SHOWER STATION - PULL CORD 9/25/2014 1
9B2100 MANUAL PRESENCE STATION - GRAY- 3 LEV 9/25/2014 3
CISCO-BRIDGE V2 Cisco Telergy Bridge with 10 User Licenses 9/19/2014 1
DISCOUNT AS10190-23% Customer Discount 7/31/2014 1
DISCOUNT 12% Discount# AS10119, Customer Discount 6/15/2014 1
ET9B2106 LAV/SHOWER STATION - PULL CORD - GRAY ET 9/25/2014 3
GPO-1 Amerinet - GPO 7/10/2014 1
GPO-3 Government - GPO 7/1/2014 1
GPO-3 Government - GPO 7/1/2014 1
GPO-3 Government - GPO 7/1/2014 1
HC-ADT HL -7 ADT INTERFACE 7/1/2014 1
HC-ADT HL -7 ADT INTERFACE 7/10/2014 1
HC-ADT HL -7 ADT INTERFACE 6/15/2014 1
HC-ANNUN ANNUNCIATOR 7/1/2014 1
HC-ANNUN ANNUNCIATOR 7/1/2014 1
HC-ANNUN ANNUNCIATOR 9/19/2014 1
HC-ANNUN ANNUNCIATOR 9/19/2014 1
HC-ANNUN ANNUNCIATOR 9/19/2014 4
HC-ANNUN ANNUNCIATOR 9/24/2014 1
HC-ANNUN ANNUNCIATOR 9/26/2014 8
HC-ANNUN ANNUNCIATOR 6/15/2014 1


When I run a pivot table, this is the result I get, which is what I'm looking for.

Invt ID Total
110-3614B 1
110-3628C 2
18A424 3
200-1073 30
200-1171 6
211-1297A 12
438-537C 52
439-0003-001 1
4A2481B 16
4A3092A 4
660422 1
660423 1
9A2220 3
9A3006 1
9B2100 3
CISCO-BRIDGE V2 1
DISCOUNT 2
ET9B2106 3
GPO-1 1
GPO-3 3
HC-ADT 3
HC-ANNUN 18

Because I'm looking at items that are needed prior to 45 days out, the list can grow and shrink on a daily basis. How could I incorporate your code into a pivot table using a macro?

Post a workbook LOL not the data to the forum! haha.

Thought I might add my two cents.

I tried mikeTron's method and it worked perfectly, but as an alternative have you thought about creating a table for your data set and then creating your pivot table from that. It should do exactly what you need and won't require VBA.

Originally Posted by BrownBoy

Thought I might add my two cents.

I tried mikeTron's method and it worked perfectly, but as an alternative have you thought about creating a table for your data set and then creating your pivot table from that. It should do exactly what you need and won't require VBA.

Tables are GREAT if you have a small file and small data and not many calculations in a workbook. If you have a complex file then I prefer to avoid the overhead of a table, but good suggestion! I forget about using tables so much since I typically play with too much data or complex models lately.