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

score 1 · Answer 1 · answered Jan 31 '18 at 01:33

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.}

Thanks Much! It seems this kind of approach should work! It did work perfectly until I renamed the tabs. Then it reverted to codename. I think. As the tabs became mismatched. I'll work on this more tonight. — Stan Hetrick, Jan 31 '18 at 18:10
Awesome! Thanks a bunch to both of you guys!! I used 2 loops and it seems to have worked!! still need to pass a range, but this should work. — Stan Hetrick, Feb 01 '18 at 16:13
Awesome! Thanks a bunch to both of you guys!! I used 2 loops and it seems to have worked!! Still need to pass a range, but this should work. (is there a place to post revised code? Not much fits here...) For x = 1 To Workbooks(WB_to).Worksheets.Count With Workbooks(WB_to).Sheets(x) tabName = Workbooks(WB_to).Sheets(x).Name MsgBox "Target… “ For y = 1 To Workbooks(WB_From).Worksheets.Count With Workbooks(WB_From).Sheets(y) If .Name = tabName Then MsgBox "Source …” — Stan Hetrick, Feb 01 '18 at 16:33

score 0 · Answer 2 · answered Jan 30 '18 at 22:55

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

This did look awesome! However... 1) there are several administrative sheets that can't be processed. So, x=6 to .count advances the start 2) I got: Compile error: Invalid qualifier. WB_From. was highlighted — Stan Hetrick, Jan 31 '18 at 17:30

Locating sheets by Name not by Codename

2 Answers2