98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Selecting a Previously ReNamed Sheet
Selecting a Previously ReNamed Sheet
Resolved · High Priority · Version 2003
Fri 7 Nov 2008, 16:01Delegate Andrew said...
Andrew has attended:
Excel VBA Intro Intermediate course
Selecting a Previously ReNamed Sheet
Hi
I have created a new sheet (Copied the Template Sheet) and renamed the sheet by using an input box
Dim NewDealName As String
NewDealName = InputBox("Deal Name")
Sheets("Template (2)").Name = NewDealName
If for Example i call this "Transaction 1" in the input box.
How do i then refer back to this sheet again?
I have tried Sheets("NewDealName").Activate
but this does not seem to work
As i want the macro to refer back to the newly created sheet each time a new deal is added e.g. Transaction 2,3,4.....
Many Thanks
Sun 16 Nov 2008, 19:37Trainer Laura said...
RE: Selecting a Previously ReNamed Sheet
Hi Andrew
Thank you for your VBA question.
You are very close to the answer, but you don't need to put quotes around the variable name. Therefor you could use
Sheets(NewDealName).Activate
I hope that helps. Please let me know how you get on.
Laura GB
Mon 17 Nov 2008, 15:08Delegate Andrew said...
RE: Selecting a Previously ReNamed Sheet
Laura
This still does not work - i get "Run-time error '9'" each time?
When i am trying to activate this sheet it is part of a different sub - does that make a difference?
e.g. i have called one sub "NewDealSheet" which creates a named sheet with an input box.
I have then called another sub "BreakdownOfDeal"
Then there is a button i have placed in excel which Calls each of these two subs (Macro called "NewDeal!" - but i get the above error each time)
How do you get around this?
thanks
andrew
Mon 17 Nov 2008, 17:14Trainer Laura said...
RE: Selecting a Previously ReNamed Sheet
Hi Andrew
Yes it will make a difference if you have the two commands in separate subroutines as the variable will have lost its value in the second subroutine.
One possible solution is to make NewDealName a public variable rather than a local to a single subroutine. Move the following line to above all the subroutines in the declaration section of the module.
Dim NewDealName As String
It should now work if you run the second routine straight after the first routine.
Let me know if it works.
Regards
Laura GB
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
