creating new worksheets sequentially

[greggy_thommo]

I have a spreadsheet which keeps track of my monthly accounts. In the one Excel document, I have a worksheet for each month e.g. Jan 07, Feb 07 etc. (so I have a whole lot of tabs). Each of these worksheets contains cells with formulas dependent on the last month, for example, balance carried forward to Feb 07 links back to end of month balance in Jan 07.

My problem is that each time I want to create a new month, I have to copy a worksheet (e.g. copy Feb 07 to produce Feb 07 (2)), rename it (e.g. Mar 07) and then change all the appropriate formulas (e.g. =Jan 07!A1 needs to be changed to =Feb 07!A1, and so on).

Is there a way to do this with a macro or something, so that when I run it, it automatically creates a new worksheet, names it as the next month, and updates the formulas?

[robert111]

This would help me, too.

You can speed it up a bit by naming A1 myname1 in the Jan sheet

Copy the sheet and rename it Feb as before, naming A1 as myname2

Then in the Mar sheet, where A1 will contain =myname1 you only need one keystroke to alter it to =myname2