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

You appear to be trying to create your pivot off of a dynamic data source.
There is a much easier way. Check this out...

http://www.contextures.com/xlPivot01.html
--
HTH...

Jim Thomlinson


" wrote:

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

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