Changing SheetTab name causes VBA to fail

[TotallyConfused]

Hello everyone.

It has been my understanding that if we referred to a sheet by its CodeName rather than its SheetTab name, then if someone renamed the sheet, it would not cause VBA to fail. With that thought in mind, I have the following code. The purpose of this macro is to open a second workbook and define the sheets in this workbook and also the second workbook. Ultimately data from sheet1 of this workbook will be copied into sheet1 of the second workbook, called 'MasterDataFile'. As this macro is written, it works great. My problem is that if I rename Sheet1 of either workbook, I then get the error message of 'Error 9, subscript out of range'. I have highlighted in red the section of code that seems to be the problem. (Note: it also includes the colon) I have tried removing the word 'ThisWorkbook' and the following period, but I still get the same error message if Sheet1 is renamed.

As a passing thought, I'd love to know if it is possible to copy a sheet from one workbook into a second workbook without opening workbook #2, and if so how, but that's a problem for another day.

Sub Open2WorkbookAndDefineSheets()
Application.ScreenUpdating = False
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Dim Wb2 As Workbook
    Dim Wb2ws1 As Worksheet
Workbooks.Open Filename:="MasterDataFile.xlsm", UpdateLinks:=0   ' Do not update links
Set Wb2 = Workbooks("MasterDataFile.xlsm")
Set Wb2ws1 = Wb2.Sheets("Sheet1")
Application.ScreenUpdating = True
End Sub

If anyone can show me what I've done wrong to cause that error message, I'd sure appreciate it. THANK YOU in advance for any help or suggestions.

TotallyConfused

 

[MARK858]

You aren't using the sheets codenames anywhere in your code, you are using the sheets tab names and setting them to variables.
To use the sheets codename in ThisWorkbook you would just use Sheet1.Name to get the tab name of the sheet.
You can't refer directly to the codename in another workbook, you need code to extract it.

Sub Open2WorkbookAndDefineSheets()
'Application.ScreenUpdating = False
Dim Wb2ws1 As Worksheet

Workbooks.Open Filename:="MasterDataFile.xlsm", UpdateLinks:=0   ' Do not update links

    With Workbooks("MasterDataFile.xlsm")
        Set Wb2ws1 = _
            .Worksheets(CStr(.VBProject.VBComponents("Sheet1").Properties(7)))
    End With

    MsgBox "The tab name of Sheet1 in ThisWorkbook is " & Sheet1.Name
    MsgBox "The tab name of Sheet1 in MasterDataFile.xlsm is " & Wb2ws1.Name

'Application.ScreenUpdating = True
End Sub

 

[TotallyConfused]

Hello Mark

THANK YOU Mark for your solution to my problem of accessing sheets in a second workbook and how to get around the problem of VBA crashing if someone changed the SheetTab name. That one line of code inside the With... End With solved everything. However, it did create a new 'problem' for me and that is since I've never seen any of those parameters, that means I now have a few more hours of studying to do. How some of you people here can remember all the solutions you give us beginners, is beyond me. You've probably forgotten more than I'll ever know about Excel and VBA.

Please accept my apology for not getting back to you sooner. THANKS AGAIN! Keep up the good work.

TotallyConfused