Creating a pivot table in VBA

R

Richard D'Angelo

Is there a way to create a pivot table in VBA that can see a variable
sized range each time it is run?

Below is the macro code I recorded, trying to see how it is done. It's
the line of code with R1C1:R8888C17 that I want to change so that will
always use the current region as its database.

Any ideas?
Rich

Selection.CurrentRegion.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"Sheet1!R1C1:R8888C17").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Object", _
"Error Description"), PageFields:=Array("Machine", "Year",
"Month")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Error
Code")
.Orientation = xlDataField
.Caption = "Count of Error Code"
.Function = xlCount
End With
Range("A6").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Object").AutoSort _
xlDescending, "Count of Error Code"
Range("B6").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Error
Description"). _
AutoSort xlDescending, "Count of Error Code"

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
 
D

Debra Dalgleish

Use a string with the address:

'=====================
Dim str As String
str = ActiveSheet.Range("A1").CurrentRegion.Address

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=str).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
'==============================
 
D

Dave Peterson

I think you have two choices:

1. Have VBA determine the range somehow--if you know the starting point (A1?)
and the last column used (say L), and can pick out a row that's always filled
(say A), you can do:

dim myRng as range
with worksheets("sheet1")
set myrng = .range("a1:L" & .cells(.rows.count,"A").end(xlup).row)
end with

'your PT code here:
....sourcedata:=myrng.address(external:=true).....

#2. Use a dynamic range that'll adjust whenever you add/delete rows or columns.

Debra Dalgleish has some notes that will explain it better:
http://www.contextures.com/xlNames01.html#Dynamic

The second one may be better if you want to allow changes to the range after the
pivottable is built.

(Lots of my pivottables only exist for moments (copy|paste special|values) and
do other stuff to it. And I use the first for that kind of thing.)
 
R

Richard D'Angelo

Thanks for putting me on the track. I created a named range and then
used that range to create the pivot table.


'Creates named range for pivot table.
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Name = "Database"
Range("A1").Select

'Creates pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"DataBase").CreatePivotTable TableDestination:="",
TableName:="PivotTable1"

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
automatic pivot tables 1
pivot table macro with a filtering variable 3
Format Pivot total rows 2
Creating a dyanmic pivot table 3
Pivot table help 8
pivot table range selection 2
syntax in VBA to create a pivot table 2

Top