Pivot tables.

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top