I have a vb app that programmatically creates an Excel template based on data
in a couple of other excel worksheets.

The main function that creates this template is posted below. In this you
will see that I have the following

objWBROI - New workbook that I am creating3
objWSCostCentres - Existing worksheet that I pull some data from to crate
new workbook
objWSTemplate - Second worksheet that I use to create new workbook

The following loop is where i add copies of objWSTemplate to my new workbook

For intCostCentre = 2 To intCostCentres
SetBudgetFigures objWSCostCentres, objWSTemplate, intCostCentre
objWSTemplate.Copy after:=objWBROI.Sheets(objWBROI.Sheets.Count)
objWBROI.ActiveSheet.Name = objWSCostCentres.Cells(RI_COSTCENTREROW,
1 + intCostCentre)
Next

My problem is that, even though objWSTemplate has the Calculate Worksheet
Automatically set, all copies of this sheet in the new workbook have this
option set to Manual. How do I overcome this?




Public Function CreateTemplate(intYear As Integer)

Const RI_COSTCENTREROW = 3

Dim objWBCostCentreBudgets As Excel.Workbook 'Workbook that contains
cost centre budgets
Dim objWSCostCentres As Excel.Worksheet 'Worksheet that contains
list of cost centres and their budgets
Dim objWSTemplate As Excel.Worksheet 'Worksheets that
contains ROI template

Dim objWBROI As Excel.Workbook 'New ROI workbook that
is generated from list of cost centre budgets

Dim intCostCentres As Integer
Dim intCostCentre As Integer

m_intYear = intYear

m_udtProps.strTemplatePath = GetYearTemplateDirectory & "\" & DEF_ROI
'TEMPLATE
m_udtProps.strBudgetLookupPath = GetYearTemplateDirectory & "\" &
DEF_COSTCENTREBUDGETS

Set objWBCostCentreBudgets = OpenCostCentreBudgets

Set objWBROI = m_objExcelApp.Workbooks.Add(GetYearTemplateDirecto ry &
"\" & DEF_ROITEMPLATE) 'new workbook for all cost-centre roi templates

'based on roi.xlt

Set objWSCostCentres = objWBCostCentreBudgets.Sheets("CostCentres")
'List of cost centres
Set objWSTemplate = objWBROI.Sheets("CostCentreTemplate")
'ROI Template

m_objExcelApp.ScreenUpdating = False

'Get number of cost centres in list
intCostCentres = objWSCostCentres.Cells(1,
objWSCostCentres.Columns.Count).End(xlToLeft).Colu mn - 1

For intCostCentre = 2 To intCostCentres
SetBudgetFigures objWSCostCentres, objWSTemplate, intCostCentre
objWSTemplate.Copy after:=objWBROI.Sheets(objWBROI.Sheets.Count)
objWBROI.ActiveSheet.Name = objWSCostCentres.Cells(RI_COSTCENTREROW,
1 + intCostCentre)
Next

'Hide template sheets
objWBROI.Worksheets(SHEET_CONTESTDATA).Visible = False
objWBROI.Worksheets(SHEET_COSTCENTRETEMPLATE).Visi ble = False

m_objExcelApp.ScreenUpdating = True

'objWBROI.SaveAs GetDirectoryName & "\" & DEF_ROI
objWBROI.SaveAs GetYearTemplateDirectory & "\" & DEF_ROI

'Tidy up
objWBROI.Close
objWBCostCentreBudgets.Close
Set objWSCostCentres = Nothing
Set objWBCostCentreBudgets = Nothing

End Function

Are you writing about .enablecalculation or you writing about the
application.calculation status.

If you're writing about the first, then change the template--or change the
setting after you create the new worksheet.

If you're writing about the second, then this is an application setting--it's
picked up by the first workbook you open in that session. But you can always
change it to automatic.

Or am I just missing the point (it's happened before!).

Terry Holland wrote:

I have a vb app that programmatically creates an Excel template based on data
in a couple of other excel worksheets.

The main function that creates this template is posted below. In this you
will see that I have the following

objWBROI - New workbook that I am creating3
objWSCostCentres - Existing worksheet that I pull some data from to crate
new workbook
objWSTemplate - Second worksheet that I use to create new workbook

The following loop is where i add copies of objWSTemplate to my new workbook

For intCostCentre = 2 To intCostCentres
SetBudgetFigures objWSCostCentres, objWSTemplate, intCostCentre
objWSTemplate.Copy after:=objWBROI.Sheets(objWBROI.Sheets.Count)
objWBROI.ActiveSheet.Name = objWSCostCentres.Cells(RI_COSTCENTREROW,
1 + intCostCentre)
Next

My problem is that, even though objWSTemplate has the Calculate Worksheet
Automatically set, all copies of this sheet in the new workbook have this
option set to Manual. How do I overcome this?

Public Function CreateTemplate(intYear As Integer)

Const RI_COSTCENTREROW = 3

Dim objWBCostCentreBudgets As Excel.Workbook 'Workbook that contains
cost centre budgets
Dim objWSCostCentres As Excel.Worksheet 'Worksheet that contains
list of cost centres and their budgets
Dim objWSTemplate As Excel.Worksheet 'Worksheets that
contains ROI template

Dim objWBROI As Excel.Workbook 'New ROI workbook that
is generated from list of cost centre budgets

Dim intCostCentres As Integer
Dim intCostCentre As Integer

m_intYear = intYear

m_udtProps.strTemplatePath = GetYearTemplateDirectory & "\" & DEF_ROI
'TEMPLATE
m_udtProps.strBudgetLookupPath = GetYearTemplateDirectory & "\" &
DEF_COSTCENTREBUDGETS

Set objWBCostCentreBudgets = OpenCostCentreBudgets

Set objWBROI = m_objExcelApp.Workbooks.Add(GetYearTemplateDirecto ry &
"\" & DEF_ROITEMPLATE) 'new workbook for all cost-centre roi templates

'based on roi.xlt

Set objWSCostCentres = objWBCostCentreBudgets.Sheets("CostCentres")
'List of cost centres
Set objWSTemplate = objWBROI.Sheets("CostCentreTemplate")
'ROI Template

m_objExcelApp.ScreenUpdating = False

'Get number of cost centres in list
intCostCentres = objWSCostCentres.Cells(1,
objWSCostCentres.Columns.Count).End(xlToLeft).Colu mn - 1

For intCostCentre = 2 To intCostCentres
SetBudgetFigures objWSCostCentres, objWSTemplate, intCostCentre
objWSTemplate.Copy after:=objWBROI.Sheets(objWBROI.Sheets.Count)
objWBROI.ActiveSheet.Name = objWSCostCentres.Cells(RI_COSTCENTREROW,
1 + intCostCentre)
Next

'Hide template sheets
objWBROI.Worksheets(SHEET_CONTESTDATA).Visible = False
objWBROI.Worksheets(SHEET_COSTCENTRETEMPLATE).Visi ble = False

m_objExcelApp.ScreenUpdating = True

'objWBROI.SaveAs GetDirectoryName & "\" & DEF_ROI
objWBROI.SaveAs GetYearTemplateDirectory & "\" & DEF_ROI

'Tidy up
objWBROI.Close
objWBCostCentreBudgets.Close
Set objWSCostCentres = Nothing
Set objWBCostCentreBudgets = Nothing

End Function

--

Dave Peterson

If you're writing about the second, then this is an application setting--it's
picked up by the first workbook you open in that session. But you can always
change it to automatic.

This is axactly what I needed to change - thanks