Creating a pivot table from a table that is independent of table/sheet name and works on any sheet.

[Deleted]

I recorded this macro using the "MAcro building" sheet in the attached worksheet (it is also the final result). It creates a pivot table (sourced from a table within the same sheet) that displays the average and standard deviation of sample measurements on the existing sheet. It needs to work for any worksheet and any data table and I'd be running the macro on multiple sheets (1 sheet/table at a time as data is generated/added) within the same workbook. Not sure how to do that. The code goes on to extract data from the generated pivot table to modify the way the data is displayed and calculate %CVs.  Please help!

VBA code below: Might need to change multiple names in the code after fixing the first few lines 

Sub INH_pivot_table()
'
' INH_pivot_table Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data", Version:=6).CreatePivotTable TableDestination:= _
"MAcro building!R2C27", TableName:="PivotTable2", DefaultVersion:=6
Sheets("MAcro building").Select
Cells(2, 27).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sample")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sample Name")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Calculated Conc."), "Count of Calculated Conc.", _
xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Calculated Conc."), "Count of Calculated Conc.2", _
xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of Calculated Conc.")
.Caption = "Average of Calculated Conc."
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of Calculated Conc.2")
.Caption = "StdDev of Calculated Conc.2"
.Function = xlStDev
End With
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.SmallScroll Down:=-18
Range("AF3").Select
ActiveCell.FormulaR1C1 = "Sample"
Range("AG3").Select
ActiveCell.FormulaR1C1 = "Average"
Range("AH3").Select
ActiveCell.FormulaR1C1 = "Stedev"
Range("AI3").Select
ActiveCell.FormulaR1C1 = "%CV"
Range("AF4").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R1C27,(ROW(R[-3]C[-5])*3)-1,0)"
Range("AF4").Select
Selection.AutoFill Destination:=Range("AF4:AH4"), Type:=xlFillDefault
Range("AF4:AH4").Select
Range("AG4").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R1C28,(ROW(R[-3]C[-5])*3)-1,0)"
Range("AH4").Select
ActiveCell.FormulaR1C1 = "=OFFSET(R1C29,(ROW(R[-3]C[-5])*3)-1,0)"
Range("AI4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]*100"
Range("AF4:AI4").Select
Selection.AutoFill Destination:=Range("AF4:AI41"), Type:=xlFillDefault
Range("AF4:AI41").Select
Selection.AutoFill Destination:=Range("AF4:AI48"), Type:=xlFillDefault
Range("AF4:AI48").Select
Selection.AutoFill Destination:=Range("AF4:AI50"), Type:=xlFillDefault
Range("AF4:AI50").Select
ActiveWindow.SmallScroll Down:=-48
End Sub

[#2]

David,
Just in case...  you don't need a pivot table to calculate Standard Deviation and Average Deviation.
Excel includes functions that return both of these values...
  = AveDev(Range) - no sample data option available
 = Stdev.S(Range) -  based on a set of sample data

Jim Cone
https://goo.gl/IUQUN2

[#3]

Thanks, James. But this doesn't really help, unfortunately.