Selecting current range for pivot macro

[Math]

I'm trying to write a macro to create a standard pivot from a report
and need some help with defining the range.

I've recorded the actions that I want using an example of the data.
It all appears to be fine and reasonably easy (if sometimes feeling
long winded), except the range selection at the start.

The start of the recorded macro looks like this...

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Base Data Report'!R1C1:R244C44").CreatePivotTable _
TableDestination:="", TableName:="PivotTable1",
DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With

I'm presuming that VBA doesn't recognise the normal "select current
range based on current cell" action of the Pivot Table wizard. In the
above, it's explicitly defined the range even though I didn't actually
have to think about that when recording.

How do I replicate the "select current range..." action for a generic
macro (because the number of records will vary each time)?

I presume the best way would be to define a variable, use some method
to push the current range into the variable, and then point the pivot
at it. But I don't know how.

Or maybe, since the columns will stay the same, I should look at
counting the rows and defining the range that way (Offset comes to
mind, but I've also read mentions of the Resize function - but, if it
is applicable, I can't find a clear explanation of how.)

Any help will be greatly appreciated.

ta
Matt

 

[DomThePom]

You need to supply a range for your data argument

The easiest way to do this is to use the currentregion method of the range
object.
Assuming your data is in a sheet called 'Data'. starting in cell A1 the
arguement would be:

sheets("Data").cells(1,1).currentregion

 

[Math]

Thanks.

How would I fit that into

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Base Data Report'!R1C1:R244C44").CreatePivotTable _
TableDestination:="", TableName:="PivotTable1",

?

Thanks again.

 

[DomThePom]

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
sheets("Base Data
Report").cells(1,1).currentregion).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1

Try this but if you still have trouble.....

I use the PivotTableWizard method of the sheet object which is a little less
complicated.

dim sht as worksheet
dim pvt as PivotTable
dim rngData as range

set rng = sheets("Base Data Report").cells(1,1).currentregion
set sht = activeworkbook.sheets.add
set pvt = sht.PivotTableWizard(xlDatabase, rngData, , "PivotTable1")

 

[Math]

Ah, wonderful. Thankyou

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        sheets("Base Data
Report").cells(1,1).currentregion).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1

Try this but if you still have trouble.....

I use the PivotTableWizard method of the sheet object which is a little less
complicated.

dim sht as worksheet
dim pvt as PivotTable
dim rngData as range

set rng = sheets("Base Data Report").cells(1,1).currentregion
set sht = activeworkbook.sheets.add
set pvt  = sht.PivotTableWizard(xlDatabase, rngData, , "PivotTable1")








- Show quoted text -