M
malotu
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:=xlDatabase, 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
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:=xlDatabase, 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