Selecting a Previously ReNamed Sheet

Resolved · High Priority · Version 2003

Fri 7 Nov 2008, 16:01 Delegate 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:37 Trainer 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:08 Delegate 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:14 Trainer 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