How to access sheets after names are changed

[TotallyConfused]

Hello all you MrExcel experts

This concerns my posting #5 & #6 in a thread started by Hitbid called ‘Question about optimizing simple code’ dated Nov. 3, 2018. The link to this is:
https://www.mrexcel.com/forum/excel-questions/1076409-question-about-optimizing-simple-code.html

I had offered a solution, then Hitbid said that the sheets were NOT named ‘Sheet1’, ‘Sheet2’ etc., as was indicated in the opening post, but that they had been renamed. Thinking my solution would still be valid, I renamed the sheets in my test workbook and ran my code again, only to find that it no longer worked. I now get ‘error msg # 9 – Subscript out of range’ on the line of ‘With Sheets(“Sheet” & X)’. This is happening since Excel doesn’t seem to make the connection between the original name of the sheets and what I renamed them.

It seems to me that quite some time ago, I read in another thread that it didn’t matter whether we renamed sheets or not, because Excel used the original default name, for example Sheet1. Perhaps I misunderstood what was being said at that time. By using Ctrl-R, I was able to display the VBAProject screen, which showed the sheets original name as well as what I renamed them to.

VBAProject (Book 1)
   Microsoft Excel Objects
      Sheet1 (MySheet1)
      Sheet2 (MySheet2)
      Sheet3 (MySheet3)
      Sheet4 (Test)
      ThisWorkbook
   Modules
       Module1

Below is my solution to the OP’s question, though I can’t take full credit for this because all I did was to modify the solution posted by Fluff.

Function B_Row1(Sht As String)
  B_Row1 = Sheets(Sht).Cells(Rows.Count, 1).End(xlUp).Row
End Function
 
Function B_Row2(Sht As String)
  B_Row2 = Sheets(Sht).Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
End Function
 
Sub Master_Finder()
  Application.ScreenUpdating = False
     Dim X As Integer
For X = 1 To 3
   With Sheets("Sheet" & X)
      .Range(.Cells(1, 1), .Cells(B_Row1(.Name), 2)).Copy Sheets("Test").Cells(B_Row2("Test"), 1)
   End With
Next
Sheets("Test").Activate
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
End Sub

My questions:
1 ) Is there any way to directly refer to the original sheet name when using VBA, so the code wouldn’t crash if a user renamed a sheet?
2 ) If #1 is not possible, then is there a way when using VBA, to access the VBAProject information to determine if anything had been renamed, and if so, to access the sheet’s new name?
3 ) If #2 is possible and using my VBAProject example given above, what code would be necessary to accomplish this? I’m wondering if somehow VBA could activate the VBAProject screen (Ctrl-R), load the results into a two-dimensional array with the original name and new name being placed in separate array elements, then scan the array picking out the needed data, but I have no clue how this could be done.

Thank you in advance for any suggestions or help you may be able to offer.

TotallyConfused

 

[RoryA]

Re 1, where you see this in the PE:

Sheet1 (MySheet1)

it means that the codename of the sheet is Sheet1 and the tab name is MySheet1. In code (within that workbook) you can simply use the code name Sheet1 in place of Sheets("MySheet1") so the code will still work if the user renames the tab.

 

[steve the fish]

If you wanted to use your loop you could use this type of constuction:

For x = 1 To 3
    For Each sh In ThisWorkbook.Worksheets
        If sh.CodeName = "Sheet" & x Then
            With sh
                'etc
                'etc

which the variable sh is then based on the sheet code name of Sheet1, Sheet2 etc rather than named Sheet1 or Sheet2.

 

[TotallyConfused]

Re 1, where you see this in the PE:

Sheet1 (MySheet1)

it means that the codename of the sheet is Sheet1 and the tab name is MySheet1. In code (within that workbook) you can simply use the code name Sheet1 in place of Sheets("MySheet1") so the code will still work if the user renames the tab.

Hello Rory

THANK YOU for explaining the relationship between the Tab name and Code name. I’ve seen these listed before, and figured they were related, but never completely understood the purpose of the Code name or what use a programmer could put it to. I’d never given much thought to changing tab names, but after experimenting with my test workbook for this thread, I can see how even the slightest change of a Tab name will bring instant disaster to the program. This is certainly something to keep in mind for any future coding. THANKS again for your help.

TotallyConfused (but maybe a smidgen less confused now)

 

[TotallyConfused]

If you wanted to use your loop you could use this type of constuction:

For x = 1 To 3
    For Each sh In ThisWorkbook.Worksheets
        If sh.CodeName = "Sheet" & x Then
            With sh
                'etc
                'etc

which the variable sh is then based on the sheet code name of Sheet1, Sheet2 etc rather than named Sheet1 or Sheet2.

Hello Steve the fish

I want to offer my hearty THANKS for your easy to understand VBA sample of how I could have written the code to avoid the problem of the user changing the Tab names. That was exactly what I needed to see to fully solve my problem. After reading the explanation given by Rory, I realized it would have to be the Code name and not Tab name that I needed to use, but I hadn’t figured out exactly how to put that bit of knowledge to use. After seeing the complete code that you provided, now it all makes sense. It was the ‘.CodeName’ part of that ‘IF’ command that was confusing, because I still hadn’t figured out how to actually access the Code name from Excel. (Duh, put the two words Code Name from Rory’s explanation, together with a dot in front and I would have had it. I know, I’m not the brightest guy around.) Once again, THANK YOU for your help.

TotallyConfused (maybe I could change my name to AlmostTotallyConfused now)