CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2412

Access Worksheet by name including ThisWorkbook

This has been bothering me for a while. I want to access Worksheet by name instead of "how worksheet has been named in Excel". I would like also to include ThisWorkbook in the line, to eliminate possibility of having several Workbooks open with Worksheets with the same name. However I am getting an error:

I have named my Worksheet CalculationItem1. Why following does not work?

Sub TestTest()
    ThisWorkbook.CalculationItem1.Range("A1:A2").Copy
End Sub

Following error appears:

enter image description here

Following works but there is a possibility of having same named Worksheet in another opened Workbook? Then errors can appear?

Sub TestTest()
    CalculationItem1.Range("A1:A2").Copy
End Sub

Here is the name:

enter image description here

Upvotes: 1

Views: 1212

Answers (3)

FaneDuru
FaneDuru

Reputation: 42256

Not sure that is possible to directly define the sheet using a specific CodeName.

But you can avoid confusing about such a sheet and another one of the active workbook, similarly named, using a function like this:

Function SetShByCN(strCodeName As String, wb As Workbook) As Worksheet
 Dim sh As Worksheet
 For Each sh In wb.Worksheets
    If sh.CodeName = strCodeName Then Set SetShByCN = sh: Exit Function
 Next
End Function

It must be adapted to send a warning in case of not existing such a sheet, or to return Nothing and this to be checked in the code calling the function. This is only made for testing reason...

It will do the job being called like in the next test code:

Sub testSetShByCN()
  Dim wks As Worksheet
  Set wks = SetShByCN("CalculationItem1", ThisWorkbook)
  Debug.Print wks.Name
  sh.Range("A1:A2").Copy
End Sub

Upvotes: 2

FunThomas
FunThomas

Reputation: 29592

As long as the sheet is in the same Workbook as the code ("ThisWorkbook"), you can access the sheet via it's code name. That is true even if the Workbook is not active - it's like you have an object-variable with that name. So using CalculationItem1.Range("A1:A2").Copy will always refer to the sheet with the code name CalculationItem1 of ThisWorkbook.

If you want to access a worksheet of another workbook via code name, you have to iterate the sheets and look for the property CodeName (as FaneDuru shows in his answer).

Upvotes: 2

Applecore
Applecore

Reputation: 4099

In your first piece of code, you need to tell Excel that you are referring to a worksheet:

Sub TestTest()
    ThisWorkbook.WorkSheets("CalculationItem1").Range("A1:A2").Copy
End Sub

Regards,

Upvotes: 0

Related Questions