excel VBA how to determine sheet index from sheet name

Question

In the ongoing project of mine, with which I'm about to be done, I want to make sure that the people taking over my job will be left with something that won't break after I'm gone. This project is an ongoing tracker kept daily throughout the year and each worksheet used (several for varying items of interest) are separated out by year in the name of the worksheet. For instance, the primary sheet is simply called 'YYYY' in which the 'YYYY' is the current year. Other worksheets are called, 'DTDYYYY', 'MTDYYYY', 'YTDYYYY' in which the YYYY is the current year. Come 2013, I'd like it if the little VBA that I use is able to take the current YYYY and find the appropriate worksheets automagically.

I know that I can reference sheets using the SheetIDX convention. I know that I can use Sheets(NAME) method. I'm not so sure how to best go about accomplishing my goal, though.

Your requirements are not clear. If you wish to find a worksheet that you know is called 2013, where is the difficulty? Do you mean you wish to create a worksheet? What happens at the moment? — Fionnuala
– Fionnuala, Commented Jan 7, 2012 at 9:19
I need to be able to dynamically use worksheets, based on the current year, that currently exist. The difficulty is that I don't know how to do it. — Jim
– Jim, Commented Jan 7, 2012 at 18:04

Tim · Accepted Answer · 2012-01-07 17:03:43Z

7

Application.Sheets("Sheet1").Index
Application.Sheets(1).Name

Hope this is what you needed.

Sorry, I didn't see the last part about current year. Perhaps this?

Application.Sheets(Year(Date())).Activate

edited Jan 7, 2012 at 17:03

answered Jan 7, 2012 at 6:03

Tim

1,0116 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jim Over a year ago

This is going the right direction for what I'm seeking. However, when use these methods, I get the error, Subscript out of range (err #9). I also tried by assigning year(date) to a variable and using that. I get the same error. I'm currently using Excel 2007. TIA!!

Fionnuala · Accepted Answer · 2012-01-07 19:48:31Z

6

You need a string for the name.

With ThisWorkbook
    .Sheets(CStr(Year(Date))).Select
End With

answered Jan 7, 2012 at 19:48

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Collectives™ on Stack Overflow

excel VBA how to determine sheet index from sheet name

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related