Ugh it had the wrong one in my clipboard. Here is the correct one.

Dim varSourceRow As Integer 'Defines which row to read data from
source file
Dim varTemplateRow As Integer 'Defines which row to write data in
template file
Dim currentMgr As String 'Defines which manager is currently
being read
Dim pivotRange As String 'Defines data range for Pivot Table
in template
Dim varSourceLen As Integer 'Defines row count of the source
file



'Turn off screen updates
Application.ScreenUpdating = False

'Load the source data and template data into memory
Set sourceWb = Workbooks.Open("C:\Documents and Settings\Test
\Desktop\ReportTest\test.xls", True, True)
Set templateWB = Workbooks.Open("C:\Documents and Settings\Test
\Desktop\ReportTest\Template.xls", True, True)

'Sets sales manager for first loop
currentMgr = sourceWb.Worksheets("Source Data").Cells(2, 3)
varTemplateRow = 2
varSourceRow = 2
varSourceLen = sourceWb.Worksheets("Source
Data").UsedRange.Rows.Count


For r = 2 To varSourceLen

'If the row in the source document has the current sales manager
If sourceWb.Worksheets("Source Data").Cells(varSourceRow, 3).Value
= currentMgr Then
For clNumber = 1 To 16

'For each column copy over the cell data to the template file
templateWB.Worksheets("Sheet1").Cells(varTemplateR ow,
clNumber).Value = sourceWb.Worksheets("Source
Data").Cells(varSourceRow, clNumber).Value
Next clNumber
varTemplateRow = varTemplateRow + 1
varSourceRow = varSourceRow + 1

'If the row in the source document does not have the current sales
manger
Else
'Define the pivot table range
pivotRange = "Sheet1!R1C1:R" & varTemplateRow - 1 & "C16"

'Refresh the pivot table with the new data

templateWB.Worksheets("Pivot").PivotTables("PivotT emp").ChangePivotCache
templateWB.PivotCaches.Create(SourceType:=xlDataba se,
SourceData:=pivotRange)

templateWB.Worksheets("Pivot").PivotTables("PivotT emp").RefreshTable

'Save the file using the sales manager name
templateWB.SaveAs "C:\Documents and Settings\Test\Desktop
\ReportTest\" & currentMgr & ".xls", FileFormat:=56
currentMgr = sourceWb.Worksheets("Source
Data").Cells(varSourceRow, 3).Value

'Clear the contents of the template file for the next cycle
and reset the template row count
pivotRange = "A2:P" & varTemplateRow

templateWB.Worksheets("Sheet1").Range(pivotRange). ClearContents
varTemplateRow = 2
End If
Next r

'Once complete close the original files and remove variable values
sourceWb.Close False
templateWB.Close False
Set rawWb = Nothing
Set templateWB = Nothing
Application.ScreenUpdating = True