Reference dynamic worksheet name in VBA code

[Tinamation]

I have a workbook with different sheets that are named by year (2017, 2018, etc) and there are named ranges within each sheet (scope is sheet level, not workbook level). I also made a Summary sheet so that i can see a quick snapshot of what was entered for a particular month in each sheet. There is a cell in the summary sheet (B2) where a date will be entered (and changed frequently). I have formulas that will use the month from B2 to pull in data from specific cells in each named range. What i would like to do with VBA code is to access only the named ranges in the sheet that has the same name as the year indicated in cell B2. So if cell B2 contained 04/01/2017, the code would use all named ranges from sheet 2017. I would then insert those named ranges into a formula which would pull in the data entered in sheet 2017 under April. I hope i explained that well enough...Is this possible?

Right now, you can see that i have entered the sheet name "2017" into the code because that is the only thing that works. But I would like have this sheet name be based on the year contained in cell B2 on the summary sheet.

Sub ListAllNames()

Dim myName As Name
Dim intCount As Integer

intCount = 6

For Each myName In Worksheets("2017").Names

Range("O" & intCount).Formula = "=index(" + myName.Name + ",2,2)"

intCount = intCount + 1

Next

Range("O1").EntireColumn.AutoFit

End Sub

This code is not finished, there will be more formulas added later but i just wanted to get the dynamic sheet name reference to work before adding all of that.

Thanks in advance!

Tina
(using Excel 2010)

 

[Zack Barresse]

Hi there, welcome to the board!

You can always use the INDIRECT formula, no VBA is needed.

=INDEX(INDIRECT(B2&"!"&NamedRange),2,2)

If you're looking to only adjust your code above, it would be something like this...

For Each myName in Worksheets(Worksheets("Summary").Range("B2").Value).Names