Sub PivotTableLoop()
Dim FinalRow As Long
Dim DataSheet As String
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
DataSheet = ActiveSheet.Name
For Each ws In ActiveWorkbook.Worksheets
NewMethod FinalRow, DataSheet
'set data range for Pivot Table
Set DataRng = Sheets(DataSheet).Range(Cells(1, 1), Cells(FinalRow, 8)) ' conversion of R1C1:R & FinalRow & C8
'set range for Pivot table placement
Set TableDest = Sheets(DataSheet).Cells(1, 9) ' conversion of R1C9
