Ref error when deleting and replacing a worksheet

  • I have to delete a worksheet and then replace it with a copy of the same worksheet, with the same name so that I can make changes to the copy. I am using formulas in a macro that reference that original worksheet but when I replace it with a copy and run the macro, the #Ref error replaces the sheet name in the formula and the error appears in the corresponding cell. I know that when you delete a sheet or rows or cells, the Ref error is generated because the reference is gone. but the replacement sheet that I am using has exactly the same sheet name as the original which I deleted. Why does the reference not work? Any assistance would be great!

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Ref error when deleting and replacing a worksheet.


    Because your formula is pointing to a point in memory. When you delete that point or sheet, your formula gets confused and replaces the sheet name with a #REF

  • Re: Ref error when deleting and replacing a worksheet.


    Thank you so much, dbrown. That makes it very clear for me.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Ref error when deleting and replacing a worksheet.-SOLVED


    If you want to continue doing what you are doing you could use the INDIRECT function
    =INDIRECT("'Sheet2'!$A$1")