CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1886

Select worksheet by variable

I'm trying to activate a worksheet based on a variable. I keep on getting a run time error code 9.

The code I have is:

c = 42705 + (b - 1) * 7
Sheets.Add(After:=ActiveSheet).Name = c
    ' Do something here

Worksheets("Import").Activate
    ' Do something here

Worksheets(c).Activate

How can I select the worksheet, if the worksheet's name will vary by the iteration?

Upvotes: 2

Views: 5610

Answers (2)

Vityata
Vityata

Reputation: 43585

The problem is that you do not use Option Explicit, thus VBA thinks that c is of Variant type. Thus, it goes converted to long at first and is not converted to string later. ALWAYS use Option Explicit.

If you add Dim c as String to your code, it should work. :)

Currently, it is looking for a sheet with index around 40000 and most probably you do not have so many.

In general - if you press the Debug button on your error message and then select C and press Shift+ F9 you would see more info about the selected variable.

Upvotes: 4

Pᴇʜ
Pᴇʜ

Reputation: 57683

I would reference the worksheet to a variable like below to avoid any issues when changing the name or index.

c = 42705 + (b - 1) * 7

Dim newWS As Worksheet
Set newWS = Worksheets.Add(After:=ActiveSheet)
newWS.Name = c
    ' Do something here

Worksheets("Import").Activate
    ' Do something here

newWS.Activate

According to VBA Best Practices you should always define and set references to all workbooks and sheets

Upvotes: 2

Related Questions