Dynamically Alter Macro Based on Tab Name or Cell Value

I have a workbook with several tabs. One tab is named for the year eg 2014. Annually, I open the workbook, rename to the new year and rename the year tab to the next year eg 2014 to 2015. All the dynamic range names update as expected. Sheet2(2014) becomes Sheet2(2015). However, a line of macro code does not change and errors out because the year does not change. The full code is:

Please Login or Register  to view this content.

The line that causes the error:

Please Login or Register  to view this content.

Is there a way to dynamically modify the '2014' sheet reference to reflect the renaming of the year tab eg to 2015 or to reference a helper cell named YEAR?
Note: the cell referenced above R332C1 is dynamic.

P.S. I have tried writing the above line many ways based on internet research but no success. I suspect I can use a LEN function to actually modify the line of code but I'm not sure its even possible to alter a macro dynamically to meet my needs here.

Any and all help will be appreciated.

Try...

Please Login or Register  to view this content.

Obviously you need to rename the tab before running the code on the first working day of the new year....

Thanks for responding. Same error. I understand that the CStr function will return the year but where does it get the year from? Is there a way to reference the year tab or a helper cell. I already have a cell on Sheet1 that automatically shows the current year tab. It is based on the Workbook file name which always reflects the current year. That cell is named 'TabYear'. It's syntax is:

Please Login or Register  to view this content.

Works for me - adds a named range referring to a sheet named, for example, 2014.

cStr () returns a string from a passed value (The 4 digit year of the current date).

cytop, this isn't working for me. Would you mind looking at a workbook with just the affected code. There must be something I am doing wrong and thanks for hanging in there with me. I note that the actual cell reference R332C1 did not change in the attached file.2015.xls

[quote]Annually, I open the workbook, rename to the new year and rename the year tab to the next year eg 2014 to 2015

A slight misunderstanding, I didn't understand you probably meant next year, but that would be a simple change to the code.

I think the following works as you want. I haven't tried to understand what you are doing, just made the code work. The assumption is there is a sheet named for next year. It checks too see if (for this year) a sheet named '2015' exists in the workbook, and if so, sets a reference to it.

If there is no sheet 2015 then a messagebox is displayed and the procedure exits.

Please Login or Register  to view this content.

this will work regardless of the year, it always checks for a sheet for next year.

cytop, wanted to tell you I appreciated the time and effort you put into your suggestions for my problem. After I reconsidered alternative approaches to what I was trying to accomplish, I found a simple piece of code that meets my needs no matter what year the tab states. I am posting the code here for anyone whom it may benefit. I am also marking the post SOLVED. Note: I marked the solution with color. Thanks again!

Please Login or Register  to view this content.