Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Selecting a Previously ReNamed Sheet
|
|
|
Bookmark this page|
The UK's most regular 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. |
Selecting a Previously ReNamed Sheet
Resolved · High Priority · Version 2003
Reply
Selecting a Previously ReNamed Sheet
HiI 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
For upcoming training course dates see: Pricing & availability
RE: Selecting a Previously ReNamed Sheet
Hi AndrewThank 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
RE: Selecting a Previously ReNamed Sheet
LauraThis 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
RE: Selecting a Previously ReNamed Sheet
Hi AndrewYes 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
RE: Selecting a Previously ReNamed Sheet
Thanks LauraI had tried making it public, but i was not running it straight after the first sub!
it all works now!
thanks for your help
Andrew
|
 |
|
» Forum post: Shared resources |
If you are using a resource pool but add resources to an indviduall project, it updates the resource pool aswell, how can you stop this
