Am I adding a potential error in VBA by renaming a Sheet name in properties? (run-time error -21473219767 (80028029))

[djaurit]

Today I ran into the Run-time error -21473219767 (80028029)
Automation error. Invalid forward reference, or reference to uncompiled type.

Like others, I had been running my code for years without issue. Today, it decided to not function as it had before. After reading a bit from other posts, I was curious if the fix was simpler? But, I’m not sure if I’m opening my code up to other issues.

Prior to this error, the sheet tab name was “Data”, the actual name in the properties was Sheet2.
The line that the error occurred on was Sheets("Data").Select

What I did: in VBA; edited the properties of the sheet in question from (name) = Sheet2 to (name) = dataSheet.
Then, used the line dataSheet.Select in place of Sheets("Data").Select

This worked... but, what potential issues might I be presenting? Best practices, thoughts, ideas are much appreciated.

For reference: Help with new Run-Time Error PLEASE!!
The fix was to declare the sheet as a variable (As Worksheet).
(Help with new Run-Time Error PLEASE!!)

 

[jsb1921]

if you have defined a variable datasheet, you should have shown that line in your post. not knowing that, the following is the way to use and will not need any variable declaration.

 Sheets("dataSheet").Select

. in the link you referred, the user declared the variable using wrong data type.

 

[Fluff]

Using the code name like that is (IMO) the best way of doing things, as long as you change the codename (which you have done) rather than using the default Sheet1, Sheet2 etc.
The only real drawback, is you can only directly refer to codenames for sheets that are in the same workbook as the code.

 

[djaurit]

Using the code name like that is (IMO) the best way of doing things, as long as you change the codename (which you have done) rather than using the default Sheet1, Sheet2 etc.
The only real drawback, is you can only directly refer to codenames for sheets that are in the same workbook as the code.

Understood, and thank you!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[milkywaykid]

I ran into the same issue as the OP with the same Run-Time Error. The macro I used for several years failed to select a worksheet today but never failed in prior years. My solution was to copy the sheet and rename it after deleting the old sheet. For example, if your worksheet is "Data" then make a copy of it "Data (2)". Delete the old worksheet "Data". Then rename "Data (2)" to "Data". The macro will work again. Somehow the worksheet name stopped being recognized.

 

[Anigito]

I ran into the same issue as the OP with the same Run-Time Error. The macro I used for several years failed to select a worksheet today but never failed in prior years. My solution was to copy the sheet and rename it after deleting the old sheet. For example, if your worksheet is "Data" then make a copy of it "Data (2)". Delete the old worksheet "Data". Then rename "Data (2)" to "Data". The macro will work again. Somehow the worksheet name stopped being recognized.

[+] I have same case.

Difference: New file has been created as a copy from the initial one.
In the initial file the code still works.
In the copied file the same code does not work.

[-] The copy/paste method for separate sheet does not work for me.

All macros are in ThisWorkbook location.
Macros are activated by buttons.
Although the macros are available through VBA viewer, I cannot select and link the macro to the button in the new file (the list is empty).

The macro opens worksheets one by one from the list defined as Array of names (as Variable), uses the data from to populate range with values in selected sheet.
I have ran code with debugger. The code runs through number of sheets, doing, what it is supposed to, and then on one of the sheets it fails with an error (above mentioned).

I understand, that this is caused by system settings, rather, than code itself. Please advise corrective actions.

Code (the very same code works on different file without errors):

Sub macroname()

' Populate table from another file (data formatted as table from Access db)
' using conditions: Sheetname (as cABBRs), Model.Suffix (as Model.Suffix), WeekNumber

Dim wARR As Variant
Dim EndRow, i, j, k As Long
Dim skuLst, abrLst, wkLst, soLst, whLst, sdLst As Range
Dim raw As Worksheet
Dim rawO As ListObject

Start:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

EndRow = Cells(Rows.Count, 4).End(xlUp).Row

Set raw = ThisWorkbook.Worksheets("IO") ' source data rests here
Set rawO = raw.ListObjects("tbl") ' source data table -- did this because of the reply above, still doesn't work

'Refrech Access DB data
rawO.QueryTable.Refresh BackgroundQuery:=False

Set abrLst = raw.Range("$C:$C")
Set skuLst = raw.Range("$D:$D")
Set wkLst = raw.Range("$A:$A")
Set soLst = raw.Range("$E:$E")
Set whLst = raw.Range("$F:$F")
Set sdLst = raw.Range("$G:$G")

For Each wARR In Array("A", "B", "C", "D", "E", "F") 'Array of KAs by sheet names

For j = 0 To 3 ' Populate data Part I
For i = 26 To EndRow
    With ThisWorkbook.Worksheets(wARR)
        .Cells(i, 11 + j) = Application.WorksheetFunction.SumIfs(whLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 11 + j)) - _
        Application.WorksheetFunction.SumIfs(sdLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 11 + j))
    End With
Next i
Next j

For k = 0 To 7 ' Populate data Part II
For i = 26 To EndRow
    With ThisWorkbook.Worksheets(wARR)
        .Cells(i, 46 + k) = Application.WorksheetFunction.SumIfs(soLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 46 + k))
    End With
Next i
Next k

For i = 26 To EndRow ' Populate data Part III
    With ThisWorkbook.Worksheets(wARR)
        .Cells(i, 1) = Application.WorksheetFunction.SumIfs(sdLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 14))
    End With
Next i

Next wARR

ThisWorkbook.Worksheets("SMRY").Range("D3") = Format(Now(), "dd/mm/yyyy HH:MM") ' Set Timestamp

Set EndRow = Nothing
Set i = Nothing
Set j = Nothing
Set skuLst = Nothing
Set abrLst = Nothing
Set wkLst = Nothing
Set soLst = Nothing
Set whLst = Nothing
Set sdLst = Nothing
Set wARR = Nothing
Set raw = Nothing
Set rawO = Nothing

Finish:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

 

[Fluff]

As this is a different question from the op, you need to start a new thread. Thanks