Multiple open workbooks pulling their names!?

**The Phil** · 08-31-2010, 06:19 PM

Hi, thanks to help I've received on this board I've been able to make an attendance sheet at work that has the ability to carry numbers forward from sheet to sheet. Each sheet is named according to a period of time, and it will look at it's own filename, and figure out what the previous file is, and then get the number from the previous file.

It works right now but the problem is that when using the =cell("filename") formula it only looks at the active workbook, which means you can't open more than one at a time. I want to make it a little bit more user friendly so I've made some code that will pull the filename which seems to work. The problem is that the filename doesn't automatically update when a new workbook is made. I have a template file that I copy and rename each week and I need the cell with the filename to automatically update whenever I open the workbook.

So basically, I need code that will make a cell recalculate upon opening the workbook. I've gotten some suggestions on this site before but they never seemed to work.

If anybody can help that would be awesome.

Thanks!

**Leith Ross** · 08-31-2010, 06:32 PM

Hello The Phil,

Posting your workbook would go a long way in helping us to help you.

**The Phil** · 08-31-2010, 07:00 PM

In this workbook you will see that opening the file will have cell D13 saying "Test2" which is what the filename used to be. I need it to automatically update to the new filename upon opening it. I also want to be able to have multiple workbooks open so they can't look at the active workbook.

There is a code in the openworkbook part that someone suggested on this forum but it doesn't seem to work.

Thanks

**Leith Ross** · 08-31-2010, 07:31 PM

Hello Phil,

Do you want to increment the file name by one? The current file is "Test2". Do you want the next file to be "Test3"? Will this be a new workbook or an existing workbook?

**Leith Ross** · 08-31-2010, 08:23 PM

Hello Phil,

If I understand what you want then will correct the problem.

Remove the Class Module, delete the code in Module1, and paste the macro below back into Module1 and save it. This will show the workbook's name on opening in cell D13.

Please Login or Register  to view this content.

**The Phil** · 09-02-2010, 03:14 PM

Sorry for not getting back sooner, I was off the last couple days.

I tried the code as you suggested and it still opens with the last filename in cell D13. I attached my file for you to see.

**The Phil** · 09-02-2010, 06:53 PM

Somehow I got it to work. I added a more specific reference to the openworkbook part that included the sheet name, and I used your code, and after fiddling around with it it started working. The odd thing is that when I try to hit alt F11 I can't see the code anywhere? But is works still...? I dunno what happened but oh well.

**mgaworecki** · 09-28-2010, 01:44 PM

I realize this is already sloved but this workbook may help someone in the future. It allows for the selection of all open workbooks from a combo box. Then once the workbook is selected, it lists the sheets of that workbook in another combo box. The rest of the work but doesn't pertain to this post but someone may find it usefull.

Mark