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