#REF error while importing a worksheet containing formulas + VBA - Excel Version 2013

Question

I have 2 workbooks, 'old' & 'new'. Both are identical (both have the same sets of worksheets - worksheet names and all - with identical formulas)

I'm trying to import Sheet A from 'old' to 'new'. The way I'm doing this is deleting the Sheet A from 'new' and then copying the whole worksheet (i.e. Sheet A of 'old' ) from 'old' to 'new'

 wkbOld.worksheets("Sheet A").Copy After:=wkbNew.Worksheets(nPos) 
'nPos is the position of Sheet A in these workbooks

Sheet A has Formulas referring to cells in Sheet B. I also have a Sheet Z that has formulas referring to cells in Sheet A.

While Importing I'm turning off Calculation for all the worksheets present in 'new' (toggleAutomaticCalculation false). I'm turning on automatic calculation after I've 'Imported' my sheet (toggleAutomaticCalculation true). I'm Using the following sub for toggling automatic calculation.

Public Sub toggleAutomaticCalculation(bToggle As Boolean)
Dim wks As Worksheet

    For Each wks In ThisWorkbook.Worksheets
        wks.EnableCalculation = bToggle
    Next wks
End Sub

After I've done my 'Importing', I find #REF Errors in Sheet Z in 'new', on those cells that had formulas referring to Sheet A, but the formulas in the now imported Sheet A are working fine (i.e. they are now linking to Sheet B in new)

My Macros for import are present in 'new', from where I'm running them.

My Questions are as follows:

Why is #REF error happening in Sheet Z while it's not happening in Sheet A, though both sheets contain formulas referring to external sheets?
Is there a way to prevent #REF errors in Sheet Z?

My only option currently is to again put back the original formulas in Sheet Z (using a separate sub) post importing.

I'm using Excel 2013 and I want this to work in Excel 2013.

Kindly help.

O. Rose O. Rose · Accepted Answer · 2015-08-10T16:28:53

I think that the #REF errors will occur in Sheet Z when you delete the original Sheet A, not when you copy in the new one. This is why Sheet Z gets #REF errors but Sheet A doesn't.

I hit this issue before and found two potential solutions. The first is to copy a range (possibly the used range) from 'old' A and paste it into 'new' A. This should avoid all #REF errors. The second option I found was to Find and Replace all = in Sheet Z with a symbol such as | before copying in Sheet A and then change them back. This is effectively re-entering the formulae but easier

Having considered the question more, I would suggest that you may need to turn off automatic updating before deleting 'old' A (if this is not what you are already doing)

#REF error while importing a worksheet containing formulas + VBA - Excel Version 2013

1 Answers