Create a pivot table in an existing worksheet

[excelexperience]

Hi guys,

Newbie to VBA here.
So I was trying to create a pivot table in an existing worksheet, but I can't seem to have it working. I'm creating a new file, based on information fill in on a Form I created.

My goal is: I have the sheet "Master" that contains the datasource. And I want to create a pivot table based on this "Master". After that, I want to populate the sheet "TestCaseID", starting in the cell B19 (because it has already the desired format in it) and create as many "TestCaseID" worksheets as the pivot table rows.

Furthermore, I wanted to populate the cell A4 of every worksheet "TestCaseID" with the corresponding row in the pivot table.

But not only is the macro not creating as many sheets as the rows on the pivot table, but also is not creating any pivot table in "TestCaseID".

Here is what I already have:


Private Sub OKButton_Click()


Dim emptyRow As Long




Dim i As Integer
Dim z As Workbook
Dim DT As Date
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Document Control").Visible = True
Sheets("Table of Contents").Visible = True
Sheets("TestCaseID").Visible = True

Set z = ActiveWorkbook
On Error Resume Next
TempFilePath = ActiveWorkbook.Path
TempFileName = WorkStream & "." & Module2 & "." & Format(Now, "ddmmyyyy")
FileExtStr = ".xls"
ActiveWorkbook.SaveCopyAs TempFilePath & "\" & TempFileName & FileExtStr
Workbooks.Open (TempFilePath & "\" & TempFileName & FileExtStr)

ActiveWorkbook.Save

ActiveWorkbook.Sheets("Table of Contents").Range("A1") = WorkStream & " | " & Module2
ActiveWorkbook.Sheets("Table of Contents").Range("A3:A33") = WorkStream
ActiveWorkbook.Sheets("TestCaseID").Range("C4") = Author2
ActiveWorkbook.Sheets("TestCaseID").Range("D4") = Now


' Create a Pivot


Sheets("Master").Select
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Master!C1:C6", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:= _
"'TestCaseID'!R19C2", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion14
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = ""
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Step")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Expected Result")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Stream").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Module").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Navigator Link").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Test Case").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Step").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Expected Result"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Test Case")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Stream")
.Orientation = xlPageField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Module")
.Orientation = xlPageField
.Position = 2
End With




Sheets("TestCaseID").Range("A:E").Copy
Sheets("TestCaseID").Range("A:E").PasteSpecial xlPasteValues


ActiveWorkbook.Sheets("Front Page").Delete
ActiveWorkbook.Sheets("Master").Delete


z.Sheets("Document Control").Visible = False
z.Sheets("Table of Contents").Visible = False
z.Sheets("TestCaseID").Visible = False

Application.ScreenUpdating = True
Application.DisplayAlerts = True



Call CancelButton_Click


End Sub




Could you guys help me out here?

 

[Legacy 367316]

I'm having the same issue. Did you ever figure it out?

 

[excelexperience]

No, not yet. I've been trying some other stuff, but unsuccessful.