In addition to my previous thread, I encounter another error: run time error - Application-defined error or object-defined error". I don't understand what it means. I would be very much appreciated if you can shred some lights on it.
I am trying to create a pivot table on an active sheet automatically.
Below is the VBA code:
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim Newsheet As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), Cells(lastrow, LastCol)).Select
Set Newsheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"2!R1C1:R3C8", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=Newsheet.Name & "!R3C1", TableName:="PivotTable.count", DefaultVersion _
:=xlPivotTableVersion12
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("EmpNo")
.Orientation = xlRowField
.Position = 1
End With
I am trying to create a pivot table on an active sheet automatically.
Below is the VBA code:
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim Newsheet As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), Cells(lastrow, LastCol)).Select
Set Newsheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"2!R1C1:R3C8", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=Newsheet.Name & "!R3C1", TableName:="PivotTable.count", DefaultVersion _
:=xlPivotTableVersion12
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("EmpNo")
.Orientation = xlRowField
.Position = 1
End With
