Recalculating Formulas after Running a Macro

tiger avatar provided me the code shown below, which allows one workbook to add sheets from another workbook. So for example, I use a program that outputs the hours billed by different employees each week. I then import the worksheet from that output file into a template workbook using the code below. The template workbook contains calculations based on the imported worksheet. The issue I am having is that the calculations are coming back as #Value!. This makes sense because until the macro runs the formula is referencing a worksheet that doesn't exist yet. However once I run the macro and import the worksheet, the formula is now referencing a worksheet that does exist, yet the calculation still comes back as #Value!. I tried using the F9 recalculate key but it does not change anything. Any ideas on how to make this work would be greatly appreciated.


Sub ImportSheetsMacro_for_hugedomer11()

Static wbDataPath As String: wbDataPath = Application.GetOpenFilename("Excel Files, *.xls*")
If wbDataPath = "False" Then Exit Sub
Application.ScreenUpdating = False

Static wbDest As Workbook: Set wbDest = ActiveWorkbook
Static wbData As Workbook: Set wbData = Workbooks.Open(wbDataPath)

Dim ws As Worksheet
For Each ws In wbData.Sheets
ws.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
Next ws
wbData.Close False
Application.ScreenUpdating = True

End Sub

I have attached simplified versions of the two spreadsheets I referenced below. Output Example.xls is what comes out of the program. Template Example.xls is the workbook that contains the macro and the sum if calculations. My goal is to be able to open Template Example.xls, click on the button that says Get Data. It will then prompt for a file, select Output Example.xls and it will take the worksheets from Output Example.xls and add them to Template Example.xls.

The problem I am having is that the formulas in Template Example.xls reference the worksheet (Hourly Billings) that is being added. They start out as #Value! because the Hourly Billings worksheet hasn't been loaded, which makes sense. However once that worksheet loads it should be able to run those calculations, unfortunately this is not the case. Does anyone have any advice on a solution to this problem?

Thanks

PS Apologies for not following the rules.

Please Login or Register  to view this content.