Inconsistency in formulas while copying a worksheet to new workbook

0
votes

I have two worksheets (named 'Q4 Financial Condition' and 'Q4 Activites') in a workbook (named 'Financial Statement Generator'), and I need to copy the worksheets into a new workbook.

Worksheet 'Q4 Financial Condition' has one cell formula as ='Q4 Activities'!F37 - H56 - H57

When I do a copy to new workbook, the formula changes to ='[Financial Statement Generator.xlsm]Q4 Activities'!F37 - H56 - H57

Is there any other alternatives in excel where I can retain the formula as it is and copy the worksheet to the new workbook.

I am doing the copy sheets through macros-

Dim sheetName As Variant
Set wb = Workbooks.Add
sheetName = Array("Q" & Quarter & " Financial Condition", "Q" & Quarter & " Activities")
ThisWorkbook.sheets(sheetName(1)).Copy Before:=wb.sheets(1)
ThisWorkbook.sheets(sheetName(0)).Copy Before:=wb.sheets(1)
1
vbaexcel
If it is one time do find [Financial Statement Generator.xlsm] and replace with empty string... – kaza

1 Answers

2
votes

Copying the worksheets one at a time will have the issue you describe. The simple way around it is to copy both sheets at once:

Dim sheetName As Variant
Set wb = Workbooks.Add
sheetName = Array("Q" & Quarter & " Financial Condition", "Q" & Quarter & " Activities")
ThisWorkbook.sheets(sheetName).Copy Before:=wb.sheets(1)