Macro to update database for next year

[Karolis]

Hello,

I have created quite a few workbooks as a database for my project. Its running very nice, but 2k11 is coming and I need to change quite a few 2010 to 2011 in my file names, cells, formulas, macros... to make this work nicely for next year.

My question would be - is it possible to create a macro to do this automatically? I understand this will be a long and complicated one, but if it's possible I would appreciate some tips. I am sort of looking for something like find&replace function macro for multiple workbooks to affect cells, graphs, formulas and macros changes.

Thanks.

[royUK]

Do you mean worksheets?

Why do you use the year in your code, give an example

[Karolis]

I use year in macros for saving files under specific names in specific folders, also to get data from some files linked to cells also graphs. I have about 80 workbooks, some have few sheets. All in one folder but in different sub-folders as in different departments, areas and types (graphs, audits, reviews). There is one main workbook which users have acces to navigate through other related workbooks, open to view or edit them, complete and save new specific workbooks in specified location to show results on related graphs.

I hope this make sence

[royUK]

Seems a bit excesive & not what i would call a database.

For future use I would have the masterbook as an addin, then either define the name as a Constant or as Named Range. Any code to change the macros will take a long time to run & will only work if the vba project is not protected

[Karolis]

What do you mean by "have the masterbook as an addin"?

[Mike1001]

What I would suggest you to do is to have a Workbook_Open macro which populates a cell named, e.g., "CurrentYear" using

Range("CurrentYear") = Year(Date)

and update the macros to take the "year" portion of sheet names, workbook names etc. as "SheetXYZ" & Range("CurrentYear"), "FilenameABC" & Range("CurrentYear") etc.
As well, you should possibly eliminate any "2010" in your code and replace it with the value of Range("CurrentYear").
This will probably cause some work now, but once this is done properly you should never have to think about years any more.
In case you might have to go back to a previous year's data, you would need to have a little macro like

SelectedYear = InputBox("Which year do you want?", "Change Year", Year(Date))
If Not SelectedYear = "" Then Range("CurrentYear") = SelectedYear

HTH