Help - How to make worksheet recalculate for chart

[Steve]

Hope someone can see the error of my way. I have a workbook saves as a
template. Each week I receive a new excel file (data dump) from payroll
timesheets of all the employee's hours charged to the different job codes
and projects they worked on for the week. With this file open, I run the
below macro. I only use two colunms, job numbers and hours from the
original file. I copy the two columns, then open the template, paste the
data into the newly created file, input the week ending date in a cell (that
cell is referenced in the chart title), then do a save, then ask for it to
calculate, then do a save as and input a new file name to it.

My problem: once the macro drops the new data in, the worksheet
automatically updates the arrays and sums the info correctly, but the
subtotal columns for the pie chart series does not recalculate. They show
#Value. After the macro has completed, I simply press the F9 key and the
sheet calculates and the chart updates without problem.

If I do everything manually the way the macro does, it works like a charm,
no need to do the F9 key. Anyone have a suggustion what command I am
missing in the macro that requires the manual F9 function after running the
macro. It has to be simple, but I can't see it. I'm running excel 2002.

Steve

Sub New_Chart()
'
Range("A2:A2000,E2:E2000").Select
Range("E2000").Activate
Selection.Copy

Workbooks.Add Template:= _
"C:\Documents and Settings\Application
Data\Microsoft\Templates\Weekly Hours.xlt"
Range("$B$2").Select
ActiveSheet.Paste

Dim UserInput1 As Variant
Dim WeDate As String

UserInput1 = InputBox("Type Week Ending Date:" & WeDate)
WeDate = UserInput1
'
Range("$A$16").Select
ActiveCell.FormulaR1C1 = UserInput1
Range("A1").Select

ActiveWorkbook.Save
' Range("A1").Select

Cells.Select
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate
Range("A1").Select

Dim UserInput As Variant
Dim RptName As String

RptName = UserInput
UserInput = InputBox("Type New File Name" & RptName)
ActiveSheet.SaveAs Filename:=UserInput
Range("A2").Select

' Range("A1").Select

' ActiveSheet.ChartObjects("Chart 1").Activate
' ActiveChart.ChartArea.Select
End Sub

 

[Juan Sanchez]

Steve

Try adding this line right at the end of the macro, right
before End Sub

Application.Calculate
End Sub

Cheers
Juan

 

[Steve]

I added it to the macro as last line, no change. I even put it there twice
hoping a second round might do it, but no luck. Still had to press F9 and
it recalculated and populated the chart.

Steve