Macro to run Pivot Table

  • Thread starter Thread starter Mikey May
  • Start date Start date
M

Mikey May

How can I use a named/dimensioned range for SourceData
instead of the R1C1 bumph.....

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:="All!R1C1:R1029C6", TableDestination:="",
TableName:="PivotTable12"

My SourceData range will be different each time I run the
macro, but can't figure out how to use specific range.
 
You could create a named range in the code, and refer to that as the
source data:
SourceData:="Database"

or use all the rows in a range of columns:

'=====================================
Dim ws As Worksheet
Dim r As Long
Set ws = Worksheets("Data")
r = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=ws.Range("A1:H" & r), _
TableDestination:="", TableName:="PivotTable12"
'==========================================
 
Back
Top