Macro to Access Worksheet Via Popup Box

[JimmiOO]

I have a macro below that gives me a popup box that lets me type in the new sheet i want to access. etc(sheet1.xls) It give me 2 boxes one i must type the old sheet i currently access etc(sheet0.xls) and the next is the new sheet i will now be accesssing. Is there a way to change this so i only get one box that will let me type in the new sheet i want to access?

Please Login or Register  to view this content.

[blane245]

You are using the term "sheet" when you may be workbook, since you refer to the name "sheet1.xls" which is actually a workbook name, not a sheet name. Assuming that you meant workbook, replace the Old_Sheet assignment in your code with

Please Login or Register  to view this content.

since that is the name of the workbook that is currently active. If you are really referring to sheets, let me know and I'll give you something else.

[JimmiOO]

Hi Blane the Old_Sheet = a closed workbook and looking for sheet1 so it wont be the active sheet.

[blane245]

Well, then reverse it an replace the New_Sheet assignment line with

Please Login or Register  to view this content.

[JimmiOO]

The workbook will change names on a regular basis will that be a problem?

[blane245]

I'm not sure which workbook you are speaking of. the old or the new one? It sounds like you are opening a workbook, saving it as a new workbook, then using your button to replace the formula with the new workbook name, then saving the new workbook again.

[JimmiOO]

I have a formula in my active sheet that looks in the oldsheet then returns a value. When my active sheet fulls up it is stored and a new active sheets, copied from the old sheet and wiped apart from the formula, the problem is the formula is still looking at an older sheet. When i press the button i need a box to come up asking me the name of the sheet i need to access, but i dont want it to ask me what the old sheet was. Hope that makes sense now.

[JimmiOO]

If it helps here is the formula i need to change. It has december.xls at the mo, this month it will be january.xls

Please Login or Register  to view this content.

[blane245]

Well, then reverse it an replace the New_Sheet assignment line with

Please Login or Register  to view this content.

I am not sure what is wrong this this solution.

[excelxx]

Hi JimmiOO

You may want to try the code below, this does assume that the sheet names will remain the same and only the workbook is changing.

Also you will need to adapt it to your workbook for the range and the initial cell to check for the current reference.

Please Login or Register  to view this content.

I hope this helps.

[JimmiOO]

That what i was after, thanks very much yet AGAIN.

[JimmiOO]

Can the admin please change this to SOLVED as i can no longer edit the first post. Thanks