How to call a changing Name?

[Thompyt]

I am trying to get the name of the sheet to copy a piece of data from one sheet to another. Sort of like using 3 tables to copy a cell into another table. It fails on the bold red code with Run-time error 1004; Application defined or object defined error.



I have tried Worksheets(WkSht.Name).Cells......, Sheets(.Name).Cells......, WkSht.Name.Cells......, Worksheets(sSheet).cells where sSheet=WkSht.name, Activesheet.cells...... and other nuances I could think of.
If there was a single worksheet then you would use: Worksheets("SheetName").Cells(x, 2) = d2(y, 2) easy peasy.

Sub HBRolestoSheets()
Dim WkBk As Workbook,  WkSht As Worksheet, WkSht2 As Worksheet, WkSht3 As Worksheet
Dim Rng As Range, Rng2 As Range, Rng3 As Range, d1 As Variant, d2 As Variant, d3 As Variant, x As Long, y As Long, z As Long



    strTab = "PC22V2"
    
Set WkBk = ThisWorkbook
    Set WkSht = Worksheets(strTab)
    Set WkSht2 = Worksheets("Baseline")
    Set Rng = WkSht.UsedRange
    Set Rng2 = WkSht2.UsedRange
    
    d1 = Rng.Value
    d2 = Rng2.Value
    x = 1
    y = 1
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False


    For Each WkSht In Worksheets
        With WkSht
            Select Case WkSht.Name
                Case Is = "Baseline", "System Count", "", strTab
                Case Else


                    For y = 2 To UBound(d2)
                    z = 1
                        For x = 2 To UBound(d1)
                            If d1(x, 2) = d2(y, 1) Then
                                z = d2(y, 6)
                            ElseIf d2(y, 6) = "" Then
                                z = 6
                            End If
                                ActiveSheet.Cells(z, 3) = d1(x, 14)
                    Next x
                Next y
            End Select
        End With
    Next WkSht



    ActiveSheet.Range("C1").EntireColumn.AutoFit
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True


End Sub

[Micron]

Put a break point on the code and step through if the used range is not too large. My guess is that at some point, z becomes zero, which would not work too well for Cells(z, 3).
Note that UsedRange includes formatting, so if cell font/border etc. is formatted, it becomes part of the range whether it contains data or not. That might be part of your problem.
If the range is large, you might want to debug.print in the loop whatever makes sense to you, such as range addresses or array values. When it errors and stops, you might be able to pinpoint the range/value/whatever that points to the cause.

[Thompyt]

Thanks Micron,
Below fixed the issue. I was using debug.print previously. it was a count and an empty cell issue. I ensured the cells were filled.

Sub AddRoleToWkSht()

    Set WkSht = Worksheets(strTab)
    Set WkSht2 = Worksheets("Baseline")
    Set Rng = WkSht.UsedRange
    Set Rng2 = WkSht2.UsedRange


    d1 = Rng.Value
    d2 = Rng2.Value
    IPCnt = 1
    y = 1
    x = 1
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False


     For Each WkSht In Worksheets
        With WkSht
            Select Case WkSht.Name
                Case "Baseline", "Acronyms", "UniqueRefer1", "UniqueRefer", "System Count", strTab, ""
                Case Else
                    For x = 2 To UBound(d1)
                        For y = 2 To UBound(d2)
                            If d1(x, 13) <> d2(y, 1) Or d1(x, 13) <> WkSht.Name Then GoTo Skip
                                If d1(x, 13) = d2(y, 1) And d1(x, 13) = WkSht.Name Then IPCnt = IPCnt + 1
                                    Worksheets(WkSht.Name).Cells(d2(y, 6) + 3 + IPCnt, 3) = d1(x, 14)
Skip:
                        Next y
                    Next x
                    IPCnt = 1
                    Sheets(WkSht.Name).Range("C1").EntireColumn.AutoFit
            End Select
        End With
    Next WkSht


    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
  
End Sub

[Micron]

I get the idea of Select Case rather than wordy If statements. Another use of Select Case could be
Case Is <> "Baseline", "Acronyms", "UniqueRefer1", "UniqueRefer", "System Count", strTab, ""

then you don't need the Case Else. You don't think that calculating the last row with data would be better than just filling the used range with data?

[Thompyt]

Remember I am just a self-taught newbie with a lot of assistance from here and internet combing. I like the case <> and will implement. Thanks
The UsedRange vs Cells(Rows.Count, 1).End(xlUp)? I understand the xlUP is more accurate than used range. Once I get the basic of my self induced project i will tweak it and probably change it.

Thanks again

[Micron]

You're welcome.
I think 99.99% of suggestions I post are meant to be strictly informative/educational and never meant to be criticism.

I am just a self-taught newbie with a lot of assistance from here and internet combing.

Ditto. I took no vba courses. When I started I didn't know how to raise a message box, so I never mock anyone else's efforts. If I see an opportunity to enhance someone else's code writing, I usually take it.

[Edgar]

Did you try using the CodeName?

Sub getAllCodeNames()
    Dim sh As Worksheet
    For Each sh In Worksheets
        Debug.Print sh.CodeName
    Next sh
End Sub

A worksheet's name is a caption. CodeName is the real name of the sheet and it's handy, for example, ActiveSheet does not give you intellisense until you declare it as a worksheet, but in VBA you can refer to it by just typing its CodeName.

Sub codeNameStuff()
    MsgBox "codename is " & Hoja1.CodeName & ", name is " & Hoja1.Name
End Sub

[Thompyt]

Edgar,
Nice info about the CodeName. I tested it and it gives the sheet## name you can see in the explorer window.

Micron,
I was not taking your comments as criticism. If any it was constructive. Appreciate this sites knowledge.

[Micron]

CodeName is certainly better than sheet index if they can move around, or sheet name if they can change names, but I don't see much benefit in CodeName wrt your post. It's good to know about the option though. Don't overlook the fact that Sheets is not the same as Worksheets.