Pivot tables.

[RET79]

OK, I am new to coding pivot tables with VBA so please bear with me.....

I recorded a macro of myself creating a pivot table. Take a look at this line:

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"NetPrem!R1C1:R244C30").CreatePivotTable TableDestination:=Range("A1"), _
TableName:="PivotTable1"

Now, the source data thing is annoying me. I don't want to pick the data up from a fixed range. I want to pick up the data from a dynamic range.

I have tried defning my range such as:


Set rng = Sheets("NetPrem").Range([a1], [a1].End(xlToRight).End(xlDown))

or even

rng = Sheets("NetPrem").Range("A1").Currentregion

but when I try and put this in the sourcedata thing as

SourceData = rng
or
SourceData = "rng"
etc
then it doesn't like it at all.

Thanks for your help,

RET79

[GeorgeB]

Try this but change the column names to suit.
(Creates a chart also)

Public Sub ByRegion()
'NOTES IN THIS SUB ARE TYPICAL OF ALL SUBS IN THIS MODULE
'Erase existing worksheet or chart if present.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Application.DisplayAlerts = False
Sheets("By Region").Delete
Charts("By Region Chart").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Select the input sheet and range A2
Worksheets("INPUT").Select
Range("A60").Select
'Create pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("A60").CurrentRegion.Address).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:="NAME", _
ColumnFields:="M", PageFields:="REGION"
ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of AMOUNT").Function = _
xlAverage
ActiveSheet.PivotTables("PivotTable1").Format xlTable4
'Rename pivot table
ActiveSheet.Name = "By Region"
'Move it to the right of the INPUT sheet
Sheets("By Region").Move After:=Sheets(3)
'Adjust column width
Range("B5:M5").Select
Selection.ColumnWidth = 7
Range("A1").Select
'Set up page for printing and add footer
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.LeftFooter = "&B Confidential&B"
.CenterFooter = "&D"
.RightFooter = "Page &P"
End With
Range("A7").Select
Do
Range(ActiveCell, ActiveCell.Offset(0, 13)).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(3, 0).Select
Loop Until ActiveCell.Value = ""
'Create chart to accompany pivot table
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("By Region").Range("A1")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveSheet.Name = "By Region Chart"
Application.CommandBars("PivotTable").Visible = False
'Add footer to chart
With ActiveChart.PageSetup
.LeftFooter = "&B Confidential&B"
.CenterFooter = "&D"
.RightFooter = "Page &P"
End With
Worksheets("INTRO").Select
Application.Calculation = xlCalculationAutomatic

End Sub