Macro or Function to add a title (ie Exhibit 1) based on worksheet index to muliple sheets

I am new to VBA and macros. I posted a similar question in a different thread and then realized it may be more suited for this thread. So my apologies in advance for the duplicate post. Here is a little background and my problem. I use excel to create financial statements and do some valuation analysis. The worksheets are then attached to a written report. Each sheet is titled as Exhibit #, based upon the order in the workbook. Typically the workbooks and the end report have less than 15 exhibits, so it is not a big deal to type in the Exhibit Number in the title cell (usually a2). On occasion l have assignments where the number of worksheets (and exhibits) can be as high as 250. Inevitably, in these situations the order of the exhibits will change and/or new exhibits will be added in the middle of the workbook. Then it becomes time consuming to manually re-title each worksheet.

I am looking for a way to automate the process so the "Exhibit #" title cells can be automatically created or updated. I tried modifying some VBA code I found online based upon the index function but I failed miserably. Any help is greatly appreciated.

I attached a sample workbook that I quickly threw together showing how I layout the workbook to give you an idea of what I am trying to do. In each worksheet cell A2 it he exhibit # I am referring to.

Hello,

May be with a code like this:

Please Login or Register  to view this content.

Hoping that it may help you,

m

Thanks this works great. The only problem I am having is when I copy your code into my personal macro workbook it no longer functions. Any thoughts?

Originally Posted by mth

Hello,

May be with a code like this:

Please Login or Register  to view this content.

Hoping that it may help you,

m

Hello,

I'm really sorry but I don't understand why it doesn't work with your other file. I think it would help us to know where the macros stops, wich line is higlighted ?

A track could be the first line "Option Explicit".
With this option, you have to declare all the variables (an exemple here)
May be you could try without this line, if it works then good (but I think it would be better to declare all the variables of your project and work with this Option Explicit)

See you soon,

m

Thanks. When I copy it into my personal macro workbook, it only changes the first sheet.

Well, this seems strange and I don't understand why it stops at the first sheet.

I'm sorry, and I hope that someone else will help you better then I do !

So sorry and good luck for your job,

m

I changed ThisWorkbook to ActiveWorkbook and that seemed to fix the problem.