It works. Thanks.
I want to do a pivot table on each sheet. What I did was to use the recording macro button to record the steps and get the VBA code. Then I try to put variable on the sheets to make it flexible. I know that the sheet would be automatically count. But I got error on the code highlighted in blue.
Do you mind take a look at my code to see how I can add the variables to the sheets and cells?
Here is the VBA code I modify:
Sub AutoPivtbl()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws 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(2, 1), Cells(lastrow, LastCol)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"2!R1C1:R3C8", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets(Sheets.Count).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("EmpNo")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("BaseRate"), "Sum of BaseRate", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Store")
.Orientation = xlPageField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End With
End Sub
