Specify a variable for a worksheet name 1

[sdiverdan]

I could not find this question in the forums. Thought it would be a simple solution. I'm using Excel 2013 and have VBA code to select a worksheet. My worksheets are years 2013,2014, 2015. Depending on the command button on my userform, I update cells in a particular spreadsheet. In my code I want the current year spreadsheet. Here is a little of my code:

Dim yr As Integer 'year variable
Dim ws, As Worksheet 'worksheet variable'

yr = Year(Now())
Set ws = ThisWorkbook.Worksheets(yr)

This fails on a Runtime error '9'- subscript out of range'. If I use "Set ws = ThisWorkbook.Worksheets("2015")" the code works. But only for the rest of this year....
I even tried "Set ws = ThisWorkbook.Worksheets(Year(Now()))" and if also fails. What am I doing wrong?

Dan

 

[SkipVought]

Hi,

A sheet name is a STRING. Your variable is an INTEGER!

dim YR As String
YR = CStr(Year(Date))

 

[sdiverdan]

That's it. Knew it was simple. I was using the year and 2015 is an integer... Thanks for the quick reply!

Dan

 

[SkipVought]

FYI,

You can use integer with the Sheets collection or ANY collection.

Suppose you have 3 sheets in your workbook. The sheet objects in the workbook are Sheets(1), Sheets(2) & Sheets(3), where the Index is an integer.

This is totally different from a sheet name.

Sheets(1) is NOT identical to Sheets("1")