Web Development

Ask something
Discussion Locked

VBA PivotTable variable range

0 helpful
5 replies

by cnwoods

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to automate the production of charts I produce on a regular basis, but although I have a standard txt download & pull it in OK, it varies as to the number of records each week, and the Pivot Table DataSource Property doesn't seem to want to accept a named range or any of the UsedRange, CurrentRange type entries. Can someone tell me how I can work around this so that each week's charts will run off the full download, but without throwing blank records into the equation?

Thanks, Crystal

5 replies

by dklippert

1. Go to Insert>Name, then choose Define.

2. In the Names in Workbook box, type a one-word name for the range, such as "MyDynamicRange".

3. In the Refers to box, use the OFFSET function to create a range that contains the number of rows equal to the number of items in column A.
This assumes there are no blank cells in column A. For example:

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

The 5 at the end of the formula is the number of columns in the range.

4. Click OK

After you create the dynamic named range, use the Pivot Table wizard to create the pivot table (Data>Pivot Table and Pivot Chart Report).
For the source, type the name of the dynamic range, "MyDynamicRange".

Here's a discussion of Dynamic Ranges:

http://tinyurl.com/26b78

by cnwoods

Had tried that. Got back a "method not supported" error. I'm ideally looking for an example script. Sheet name is Week1, columns A through AS, OS is Windows 98, running Office XP - I have a worksheet defined for the tables (Week1TAB) and another for the charts - there are 4 of them, but I get the remaining to feed off the first Pivot table which I call Week1Day (counts # of entries for each day of the week, beginning Sunday. Remaining sheets chart time (grouped in 2 hour lots), type and suburb. Wondered if a standard chart might be better, but because I never know which type or suburb entries will come up, wasn't sure how to do that, and pivot makes it easy. Well, except for automating it to a variable range...

Thanks
Crystal

by cnwoods

Yes, I do, but while I was investigating this myself found the solution offline (browsing a bookstore). For reference anyone else with the same problem, the answer was

Sub CreatePivotTable()

Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase,
SourceData:=Range("A1").CurrentRegion.Address)
Set PT = PTCache.CreatePivotTable _
(TableDestination:="", TableName:="PivotTable1")
With PT
.PivotFields("Region").Orientation = xlPageField
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("Sales").Orientation = xlDataField
End With
End Sub

This discussion is closed to new replies.