Sub UtilizeTemplate(templatefilenm As String, resultfilenm As String) Dim inpmth As String, totrows As Long, totcols As Long Sheets("input").Select totrows = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row totcols = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column Call InputIntoTemplate(ActiveWorkbook.Name, ActiveSheet.Name, 1, 1, totrows + 0, totcols + 0, templatefilenm, resultfilenm) Sub InputIntoTemplate(wkbooknm As String, wksheetnm As String, X1 As Long, Y1 As Long, X2 As Long, Y2 As Long, templatefilenm As String, resultfilenm As String) Dim objpivotfield1 As PivotField, objpivotfield2 As PivotField Dim objpivotitem1 As PivotItem, objpivotitem2 As PivotItem Dim TopRow1 As Long, TopRow2 As Long, LastRow As Long Dim LeftColumn As Long, RightColumn As Long Dim var01 As String, var02 As String, var03 As String, var04 As String, var05 As String, var06 As String, var07 As String, var08 As String, varH1 As String, varH2 As String, varR1 As String, tempv As String, tempzz As String workingwb = ActiveWorkbook.Name Workbooks.Open Filename:=templatefilenm templatewb = ActiveWorkbook.Name Windows(workingwb).Activate tempzz = 0 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:="" & ActiveSheet.Name & "!R" & X1 & "C" & Y1 & ":R" & X2 & "C" & Y2, Version:=xlPivotTableVersion10).CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 var01 = ActiveSheet.PivotTables("PivotTable1").PivotFields(7).Name var02 = ActiveSheet.PivotTables("PivotTable1").PivotFields(8).Name var03 = ActiveSheet.PivotTables("PivotTable1").PivotFields(9).Name var04 = ActiveSheet.PivotTables("PivotTable1").PivotFields(10).Name var05 = ActiveSheet.PivotTables("PivotTable1").PivotFields(11).Name var06 = ActiveSheet.PivotTables("PivotTable1").PivotFields(12).Name var07 = ActiveSheet.PivotTables("PivotTable1").PivotFields(13).Name var08 = ActiveSheet.PivotTables("PivotTable1").PivotFields(14).Name var09 = ActiveSheet.PivotTables("PivotTable1").PivotFields(15).Name varH1 = ActiveSheet.PivotTables("PivotTable1").PivotFields(1).Name varH2 = ActiveSheet.PivotTables("PivotTable1").PivotFields(16).Name varR1 = ActiveSheet.PivotTables("PivotTable1").PivotFields(2).Name ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var01), var01 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var02), var02 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var03), var03 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var04), var04 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var05), var05 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var06), var06 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var07), var07 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var08), var08 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(var09), var09 & " ", xlSum ActiveSheet.PivotTables("PivotTable1").DataPivotField.Orientation = xlColumnField ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array(varR1), PageFields:=Array(varH1, varH2) Set objpivotfield1 = ActiveSheet.PivotTables("PivotTable1").PivotFields(Index:=varH1) Set objpivotfield2 = ActiveSheet.PivotTables("PivotTable1").PivotFields(Index:=varH2) For Each objpivotitem1 In objpivotfield1.PivotItems Windows(workingwb).Activate 'get source pivot ActiveSheet.PivotTables(1).PivotFields(varH1).CurrentPage = (objpivotitem1) If (objpivotitem1) = "(blank)" Then Else For Each objpivotitem2 In objpivotfield2.PivotItems Windows(workingwb).Activate 'get source pivot ActiveSheet.PivotTables(1).PivotFields(varH2).CurrentPage = (objpivotitem2) If (objpivotitem2) = "(blank)" Then Else With ActiveSheet.PivotTables(1) TopRow2 = .TableRange2.Row With .TableRange1 TopRow1 = .Row LastRow = .Rows.Count + .Row - 1 LeftColumn = .Column RightColumn = .Columns.Count + .Column - 1 End With End With vArrdata = Range(Cells(TopRow1, LeftColumn), Cells(LastRow, RightColumn)).Value2 Windows(templatewb).Activate 'return to output template file to plot data Sheets("template").Copy After:=Sheets(1) ActiveSheet.Name = objpivotitem1 & "_" & objpivotitem2 If LastRow = 6 Then Cells(1, "C").Value = "" Else For i = 4 To 34 Cells(i, "A").Select datv = Selection.Value headerontemplate = 3 totoutputcol = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column For jj = LBound(vArrdata) To UBound(vArrdata) Step 1 If vArrdata(jj, 1) = datv Then For Z = 2 To totoutputcol Cells(i, Z).Select tempv = Cells(headerontemplate, Z).Value If Z = totoutputcol And UCase$(tempv) = "TOTAL" Then Selection.FormulaR1C1 = "=sum(RC[-1]:RC[" & 2 - Selection.Column & "])" Else For zz = 1 To RightColumn If UCase$(Trim(vArrdata(2, zz))) = UCase$(tempv) Then tempzz = zz Exit For Else End If Next zz If tempzz > 0 Then Selection.Value = vArrdata(jj, tempzz) Else End If tempzz = 0 End If Next Z 'Cells(i, "B") = vArrdata(jj, 2) 'Cells(i, "C") = vArrdata(jj, 3) 'Cells(i, "D") = vArrdata(jj, 4) 'Cells(i, "E") = vArrdata(jj, 5) 'Cells(i, "F") = vArrdata(jj, 6) 'Cells(i, "G") = vArrdata(jj, 7) 'Cells(i, "H") = vArrdata(jj, 8) 'Cells(i, "I") = vArrdata(jj, 9) 'Cells(1, 1).Value2 'Cells(i, "J").Select 'Selection.FormulaR1C1 = "=SUM(RC[-1]:RC[-8])" Exit For Else End If Next jj Next i End If End If Next objpivotitem2 'loop to next type+FileName End If Next objpivotitem1 Windows(workingwb).Activate Sheets("input").Select Windows(templatewb).Activate ActiveWorkbook.SaveAs Filename:=resultfilenm ActiveWorkbook.Close savechanges:=False End Sub