Pivot Table changing sum values using VB
I have a pivot table that i am trying to copy out to another sheet.
A note of clarification.
My variables are used in the sum field - I have 70 different columns of
data, that I want to use in combination one column field and one row field.
So each time I'm changing the sum calculated by add a new sum and remove the
previous one.
I've tried to record the procedure to highlight a new variable and remove
the previous. However it doesn't seem to work.
I've seen a post that talks about calculated fileds but this isn't.
Any suggestions gratefully received.
Code:
Sub pivot1()
Dim j As Integer
Dim k As Integer
Dim i As Integer
Dim StrM As String ' new variable
Dim StrM1 As String ' old variable
Dim StrN As String
Dim theBook As Workbook
Dim theSheet As Worksheet
Dim theSheet1 As Worksheet
Set theBook = ActiveWorkbook
Set theSheet = theBook.Sheets("Sheet4") ' this is where my data is
Set theSheet1 = theBook.Sheets("Sheet3") ' this is where my new table is
k = 2 ' column in my new table
j = 1
For i = 1 To 70
StrM1 = StrM
StrM = "SCT43000" & j
StrN = "Sum of SCT43000" & j
If i < 2 Then
theSheet.PivotTables("PivotTable2").AddDataField
theSheet.PivotTables( _
"PivotTable2").PivotFields(StrM), StrN, xlSum
Else
theSheet.PivotTables("PivotTable2").AddDataField
theSheet.PivotTables( _
"PivotTable2").PivotFields(StrM), StrN, xlSum
theSheet.PivotTables("PivotTable2").PivotFields(StrM1).Orientation = xlHidden
End If
theSheet. Range("B4:F637").Copy (theSheet1.Cells(1, k))
j = j + 1
k = k + 5
Next i
End Sub
This Is the macro recorders code
ActiveSheet.PivotTables("PivotTable2").PivotFields("SCT4300068").Orientation
= xlHidden
' this seems to remove the values but not as part of a macro.
|
