Locating sheets by Name not by Codename

Question

Using the Name of the sheet Not the Codename, I'm trying to make an update/Import kind of routine. Copy from Source (wb_From.sheet) and paste into Target (wb_To.sheet) with the same Name. My code is locating each tab name in the Target BUT not Finding the same sheet name in the source (with different Codename). Any Suggestions?

Dim WB_To as String  'Target - WB to copy into
Dim WB_From as String  'Source- WB to copy from

For x = 1 To Workbooks(WB_To).Worksheets.Count

    'Select Target sheet
    Workbooks(WB_To). Sheets(x).Activate
    tabName = Sheets(x).Name

    'activate Source WB with same tab name
    Workbooks(WB_From).Sheets(tabName).Activate

Answer 1

A typical referencing problem scenario so you might want to check on this post which explains the benefits of not using Activate, Select and the like. Now going to your problem, this line:

Workbooks(WB_To).Sheets(x).Activate 

activates Sheet(x) yes, and you can actually get it's name by:

tabName = Sheets(x).Name

But you are looping, and who knows which workbook you are actually working on (although you made sure you inserted the Activate method on the right places). Might as well abandon Activate. Refactoring your loop:

For x = 1 To Workbooks(WB_To).Worksheets.Count
    With Workbooks(WB_To).Sheets(x)
        '/* do what you need to do */
        Msgbox "Sheet " & .Name & " being processed from destination WB."
        With Workbooks(WB_From).Sheets(.Name)
            '/* your code here */
            MsgBox "Sheet " & .Name & " found on source WB."
        End With
    End With
Next

Didn't change much, just eliminated the use of activate. Not the most elegant solution, you can actually adopt to any alternative outlined in the link posted above, but I hope this gets you started.

^{Important: This solution doesn't account the possibility of mismatch sheet names or sheets that doesn't really exist on the source workbook. You will need to add a check to cover that too.}

Answer 2

Instead of looping through sheet indexes (which will almost definitely not line up across workbooks) loop through the worksheets collection and get the name (not the codename). It's not guaranteed to fix your issue, but it will make the locals window easier to navigate and your issue a little easier to trace.

Dim WB_To as String  ‘Target - WB to copy into
Dim WB_From as String  ‘Source- WB to copy from
Dim ws as worksheet

For Each ws in Workbooks(WB_To).Worksheets

    'Select Target sheet (which will be the ws)
    ws.Activate        

    'activate Source WB with same tab name
    WB_From.Sheets(ws.name).Activate