Macro: Save ActiveSheet in New Workbook - As Values (INDIRECT function issue)

I have a spreadsheet with multiple tabs, one for each sales rep, that calculates sales totals on a monthly basis.

The first tab is the full summary, outlining the pay each rep received for a specific month.

I have provided a very simplified example file, but there's enough provided to bring forth the issue at hand.

In the attached file, select the month to be paid out in cell G2
- column E will reference the values in both Column C and G2
- column E uses the INDIRECT function to reference the name of the tab each rep's dollar amount resides in --- but this is where the problem is

I have a macro set up to create and save the summary tab in a new workbook with the code below:

Sub btnSavePay()

ActiveSheet.Copy
With ActiveSheet.UsedRange
    .Copy
    .PasteSpecial xlValues
    .PasteSpecial xlFormats
End With
Dim stDocName As String
stDocName = ActiveSheet.Name
Application.Dialogs(xlDialogSaveAs).Show "C:\Desktop\" & stDocName & ".xlsx"
End Sub

However, the file that is created returns none of the results that the INDIRECT function is calculating. At first, I was getting #REF! errors when running the macro, so I threw in IF(ISERROR, but that only replaced the #REF! error with zeros, even when there was no error or any reason why the cell should be errored out to zero.

Is there something I'm missing in the macro? Is it just not possible to use the INDIRECT function in a situation like this?

Usually is that easy, isn't it?

Thank you Norie.