Fully reference a worksheet by codename
Referring to a sheet by its codename always implies the sheet in ThisWorkbook
, i.e. the workbook that contains the code you are executing.
There seems to be no straightforward way to fully qualify a sheet in a different workbook using its codename.
This function will help you do this:
Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet
Dim iSheet As Long
If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
For iSheet = 1 To wb.Worksheets.Count
If wb.Worksheets(iSheet).CodeName = worksheetCodename Then
Set GetSheetWithCodename = wb.Worksheets(iSheet)
Exit Function
End If
Next iSheet
End Function
Example usage:
GetSheetWithCodename("Sheet1", Workbooks("Book2")).Cells(1, 1) = "Sheet1 in Book2"
GetSheetWithCodename("Sheet1", ActiveWorkbook).Cells(1, 1) = "Sheet1 in ActiveWorkbook"
GetSheetWithCodename("Sheet1").Cells(1, 1) = "Sheet1 in ThisWorkbook"
Note that the last line is equivalent to simply saying:
Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook"
because, as mentioned above, referring to a sheet by its codename always imply the sheet in ThisWorkbook
.
You can do this by adding the second Workbook as a reference, and calling the workbook by the VBA Project name.
Obviously, it's a good idea to change the VBA project name from the default 'VBAProject'!
I tested this by creating two new workbooks each with one sheet, with a value of 1 or 2 in cell A1. The VBA projects were named 'Proj1' and 'Proj2', and the worksheets' CodeNames were left as 'Sheet1'.
This is the code I used:
Sub test()
Debug.Print Proj1.Sheet1.Cells(1, 1)
Debug.Print Proj2.Sheet1.Cells(1, 1)
End Sub
Yielding an output of:
1
2