Referencing code name of second workbook vba

[jon1315]

Morning All
I have two workbooks. The active workbook does some calculations/ has some user input, and then a macro is used to move certain entries to a second workbook.

Both workbooks are set up using code names as somebody will inevitably change the worksheet name at some point (!) but when I try and reference the code name of the second workbook I get a "Compile error: Argument not optional"

I've attached my code below - it's the last two rows that seem to be the problem. If I take my option explicit off then the code will open the WBSuspense, but not be able to find "Log" (which is the correct codename)

just wondering if anyone has any idea what I've done wrong?

Thanks in advance!
Jon

Sub Move_To_Suspense()
Dim LRow, LRowSuspense As Long
Dim WBMaster, WBSuspense As Workbook
Dim SuspenseFP As String
Set WBMaster = ThisWorkbook
SuspenseFP = Range("suspensefp")
    HHC.Activate
LRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("a2:i" & LRow).AutoFilter field:=9, Criteria1:="Suspense"
        If Range("a2:a" & LRow).SpecialCells(xlCellTypeVisible).Count = 1 Then
                HHC.ShowAllData
                Instructions.Select
                MsgBox "No rows to move to suspense"
        Exit Sub
        End If
Set WBSuspense = Workbooks.Open(Filename:=SuspenseFP)
    Log.Activate

 

[Norie]

You can only use the codename to refer directly to a sheet that's in the same workbook as the code.

 

[Fluff]

You can only directly refer to codenames like that, for the workbook containing the code.
One workround is to loop through the sheets in the other workbook looking at their code name & then set that as a variable.

Set WBSuspense = Workbooks.Open(Filename:=SuspenseFP)
For Each ws In WBSuspense.Worksheets
   If ws.CodeName = "Log" Then Exit Sub
Next ws
ws.Activate

 

[RoryA]

You can refer to code names in other workbooks but only if you have a reference set to the other workbook's project (which requires having renamed it to something other than the default VBAProject).

 

[jon1315]

You can only directly refer to codenames like that, for the workbook containing the code.
One workround is to loop through the sheets in the other workbook looking at their code name & then set that as a variable.

Set WBSuspense = Workbooks.Open(Filename:=SuspenseFP)
For Each ws In WBSuspense.Worksheets
   If ws.CodeName = "Log" Then Exit Sub
Next ws
ws.Activate

Perfect - just changed the exit sub to exit for and it seems to work fine!

 

[jon1315]

You can refer to code names in other workbooks but only if you have a reference set to the other workbook's project (which requires having renamed it to something other than the default VBAProject).

Looks like something for me to look into - every time I'm on here I realise just how little I know!

Thanks all!

 

[Fluff]

Glad we could help & thanks for the feedback.