#Ref! Error

[Guzzlr]

Hello
I am getting the #Ref! Error because I have a formula in one sheet, that is linked to another sheet that I delete.
When I replace the deleted sheet with an identical sheet with new data, I get the #Ref! error.
How can I retain the formula that is linked to the deleted sheet, when an identical (same column and data only with new data) sheet is placed so the original formula can see it again?
Thank you

 

[offthelip]

probably the easiest way to do it is: before deleting the sheet; on the sheet with the formula, do a global change of "=" to "A=" , this will change all the formula to text.
then delete the sheet and move the identical sheet in with the new data, then on the sheet with formula do a global change of "A=" back to "=" and thus restore all the formulae

 

[Alex Blakenburg]

The more common approach would be not to delete the sheet in the first place but to delete the data in the sheet and copy in the new data.
An alternative might be to use power query to get your data from a data source workbook into that sheet and you replace that workbook with the one with the new data and refresh your query.

 

[Guzzlr]

The more common approach would be not to delete the sheet in the first place but to delete the data in the sheet and copy in the new data.
An alternative might be to use power query to get your data from a data source workbook into that sheet and you replace that workbook with the one with the new data and refresh your query.

So the more common approach I tried.
Instead of deleting the sheet, I cleared the sheet. My formulas are still there, however when I paste in new data, at the same area, the formulas do not pick it up...the cells read 0

Here is my code to delete and clear my sheets.

Option Explicit
Dim sht As Worksheet
Dim ans As Long

Sub ClearContents()


'In case of no numeric formula or constants
On Error Resume Next

'Procedure
    For Each sht In ActiveWorkbook.Worksheets
        If (sht.Name = "Report Paste") Then sht.Delete
    Next sht
    Worksheets("PM Schedule Repair").Cells.Clear

'Disables any error trapping currently present in the procedure
On Error GoTo 0

'Return to Template Tab
Sheets.Add(After:=Sheets("Instructions")).Name = "Report Paste"

Sheets("Report Paste").Select
Range("A1").Interior.ColorIndex = 4
Range("A1").Select

Application.ScreenUpdating = False
Application.DisplayAlerts = True

End Sub

The sheet labeled "Matrix" is where my CountIfs are, which remain...they just are not picking up the data when the new data is pasted in.

Thanks for the help

 

[RoryA]

Which sheet are the countifs formulas looking at, and do the formulas look correct after the code has run and the data has been pasted in?

 

[Guzzlr]

Which sheet are the countifs formulas looking at, and do the formulas look correct after the code has run and the data has been pasted in?

the countifs are in a sheet called matrix. The sheet where data is cleared, and then repasted is in another sheet called PM Schedule Repair.
So when I paste the new data in the cleared sheet, which is identical that was previously cleared, the Matrix sheet with the countifs is all zero's.
However, when I copy and paste the countif formulas from an older copy, the data comes back correctly.

 

[RoryA]

Do the formulas look correct after the code runs?

 

[Guzzlr]

the countifs are in a sheet called matrix. The sheet where data is cleared, and then repasted is in another sheet called PM Schedule Repair.
So when I paste the new data in the cleared sheet, which is identical that was previously cleared, the Matrix sheet with the countifs is all zero's.
However, when I copy and paste the countif formulas from an older copy, the data comes back correctly.

I think I found the problem. The file is synced with OneDrive. So when I look at the Countif formula, it has the typical countif formula mixed with a lot of webaddresses coming from OneDrive. When i paste the countif formulas from an earlier worksheet, the formulas do not have all the combined webaddress addded in, just the Countif formula, and all the data comes back. So I need the file to save locally on my hard drive, with out all the webaddress in the countif formula.

 

[Guzzlr]

'Find and Place "Service Provider" Column
Set fnd = Range("5:5").Find("Service Provider", , , xlWhole, , , False, , False)
If Not fnd Is Nothing Then
    Intersect(Range("5:" & Cells.Rows.Count), fnd.EntireColumn).Copy
    Range("O5").Insert Shift:=xlToRight
        Intersect(Range("5:" & Cells.Rows.Count), fnd.EntireColumn).Clear
        Range("B:B").Delete
        Range("N:N").Delete

The code above is what is causing the problem. when that code runs, it finds and moves the "Service Provider" column from D:D to the back. Therefore, the countifs formula is incorrect, and instead of looking in D:D range, it moves to C:C range, therefore, the criteria is in the wrong criteria range.
Is there a way to keep the formula in the Matrix worksheet locked, so it does not change when a column in the lookup sheet is moved?
In other words. column D:D is the correct column, however it changes to C:C from the code above cutting and moving the column to the end of the data set.
Thank you

 

[RoryA]

I'd suggest you use code to repopulate the formulas.