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. Any suggestions gratefully received.
I enclose my code below.
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.