Pivot table with a variable range in the sourcedata field
Hi,
A few approaches for you;
1) If you are just adding records to existing columns (ie not adding fields
to the PivotCache) then you might try to simply define the UsedRange of your
pivot table as all the rows for the columns you use.
e.g. if your UsedRange is currently A1:L3500, redefine the UsedRange as A:L.
Then you only have to use the .RefreshTable method to bring in new data.
2) Use a dynamic range. eg Suppose your data is in the range
"A1:G19", then define a new range name called AcData with
the following formula;
=Sheet1!$A$1:INDEX(Sheet1!$G:$G, COUNTA(Sheet1!$A:$A))
This assumes that column headings are in row 1, and that
column A contains a value for every row in the data range
ie no null values or blanks.
The dynamic range works because COUNTA(Sheet1!$A:$A) gives
the total number of rows, and INDEX(Reference,RowNo) points
to the cell in 'Reference' given by 'RowNo'. In our case
'Reference' is all of column G, so if there are values in
A1 to A19, the INDEX function would point to cell G19.
Let's go one further, if you define the range name AcData
as;
=Sheet1!$A$1:INDEX(OFFSET(Sheet1!$A:$A,0,COUNTA(Sh eet1!$1:$1)-1),
COUNTA(Sheet1!$A:$A))
(all on one line). Then you only need continuous column headings
in row 1, and continuous non-blank data in column A. The pivot
table range will be adjusted automatically if you add rows or
add column headings.
To add a named range, use the menu items Inser -Name-Define...
enter the name for the range and the formula, then click Ok.
3) Similar to (2) you can use the OFFSET function to define a
dynamic range.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)
This is nice because you can easily make the range dynamic in
both dimensions.
=OFFSET('Sheet1!$A$1,0,0,COUNTA('Sheet1'!$A:$A),CO UNTA('Sheet1'!$1:$1))
4) Base the pivot table on a named range, say ptData, and change the address
of the named range.
Sub UpdatePivot()
' change the named range dimensions
Call ChangeRange("Sheet1")
' update the PT
ThisWorkbook.Worksheets("PivotSheet").PivotTables( "PivotTable1").RefreshTable
End Sub
Sub ChangeRange(shNm As String)
Dim strDataAddress As String
strDataAddress = "=" & shNm & "!$A$1:$C$7"
ThisWorkbook.Names.Add Name:="ptData", RefersTo:=strDataAddress
End Sub
5) In your code insert this line
r = "Sheet1!R1C1:R" & s & "C5"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=r).CreatePivotTable _
TableDestination:="", TableName:="pt1",
DefaultVersion:=xlPivotTableVersion10
Ed Ferrero
Hi
I need to use a variable in this code to changue de range data used to
create a pivot table.
I would need to include the variable s in stead of using R70 in the
line below:
SourceData:= _
"Sheet1!R1C1:R70C5"
This is the whole code:
Private Sub CommandButton3_Click()
Dim i As Double
Dim s As Double
Dim r As String
i = 1
s = 0
'find the last row with data
Do While i < 2998
If Sheet1.Cells(i, 1) = 0 Then
i = i + 1
If Sheet1.Cells(i, 1) = 0 Then
s = i - 2
GoTo f:
End If
Else
i = i + 1
End If
Loop
f:
' create de pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R70C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array( _
"Schedule Group", "Sub Group"), ColumnFields:=Array("Year",
"Build Week")
ActiveSheet.PivotTables("PivotTable1").PivotFields ("SumOfQty
Outstanding"). _
Orientation = xlDataField
End Sub
Can anybody help me please.
I can't find the way to do it.
Thanks
|