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

**Joven76** · 09-16-2014, 02:17 PM

I am building a macro to create a pivot table based off a range of data, however this range could change from day to day (ie sometimes more rows, sometimes less, but always the same number of columns).

The macro I built uses a specific range, but I would like the macro to "select all" data in the workbook instead to create the pivot table. I noticed if I have blank rows in the range, it messes up the pivot table. Is there a way to have the code always select all information in the worksheet rather than a specific range of cells?

Does this make sense?

**mikeTRON** · 09-16-2014, 02:38 PM

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.

**Joven76** · 09-16-2014, 02:39 PM

How do I do that?

**mikeTRON** · 09-16-2014, 02:49 PM

So the dynamic range works like this

=OFFSET(DATA!$A$1,0,0,COUNTA(DATA!$A:$A),COUNTA(DATA!$1:$1))

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.

**Joven76** · 09-16-2014, 03:04 PM

One side note, I went ahead and tried to set up the macro with a named range, however it set up a specific range of cells (ie: A2:D375) as the range. I know if I have another day where there's more or less information, it will either miss it or screw up the pivot table. Any suggestions?

**Joven76** · 09-16-2014, 03:15 PM

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?

**swade730** · 09-16-2014, 03:55 PM

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?

See my past post on pivot tables

**mikeTRON** · 09-16-2014, 04:02 PM

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.

**Joven76** · 09-16-2014, 04:21 PM

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?

**Joven76** · 09-16-2014, 03:00 PM

Thanks for your help. I found this in my searches, and it seems to work just fine selecting the cells I want, and it will change every time. Now how do I use this range in my pivot table?

Dim rangeTemp As Range
Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rngTemp Is Nothing Then
    Range(Cells(1, 1), rngTemp).Select
End If

I noticed that there is a range in the pivot table code, so I deleted it and changed it with the rngTemp name, but got errors. isn't this ranged named rngTemp or do I need to do something else like actually name the range and use that range of cells name for the pivot table?

Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "rngTemp", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invt ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("BO qty"), "Sum of BO qty", xlSum

**mikeTRON** · 09-16-2014, 03:02 PM

Did you even try my method?

**Joven76** · 09-16-2014, 03:08 PM

Not yet Mike. I found that code and was typing everything up while you were typing your response, so I didn't see it until after I had finished my post. I'm giving it a go now.

**mikeTRON** · 09-16-2014, 03:14 PM

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

**mikeTRON** · 09-16-2014, 04:38 PM

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

**Joven76** · 09-16-2014, 04:42 PM

BackOrder Report By Order No 40740ascombo.xls

Oh, yea, I knew that... Duh!

**Joven76** · 09-17-2014, 09:48 AM

Hey Mike,

My apologies for thinking your method wouldn't work. After doing more research last night and this AM on what Dynamic Ranges were, I fully understand what you were talking about now. I tested it, rebuilt the macro, and it worked perfectly! Now onto the next step of this project.

Just in case anyone else was wondering how it was done, I've posted the code below:

' Create DynamicRange name BacklogRange
    ActiveWorkbook.Names.Add Name:="BacklogRange", RefersToR1C1:= _
        "=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1,4))"
    ActiveWorkbook.Names("BacklogRange").Comment = ""
    With ActiveWorkbook.Names("BacklogRange")
        .Name = "BacklogRange"
        .RefersToR1C1 = "=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),4)"
        .Comment = ""
    End With
    
' Create Pivot Table
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "BacklogRange", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Invt ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("BO qty"), "Sum of BO qty", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False

Thanks again Mike!

**BrownBoy** · 09-17-2014, 10:06 AM

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.

**mikeTRON** · 09-17-2014, 10:23 AM

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.

**Joven76** · 09-17-2014, 10:16 AM

For my particular project, I need VBA because this is just a small portion of a macro I'm building to compile 3 to 4 reports into one summary workbook.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Changing data range in Pivot table

[SOLVED] Dynamically Changing Position of Pivot Based on Size of Another Pivot Table

[SOLVED] Pivot Table VBA Changing Data Range

changing range selection for pivot table based on worksheet

Creating two different charts based on a single pivot table based on condition

Bookmarks

Bookmarks

Posting Permissions