Switching between worksheets

[khansen97]

I have a spreadsheet that I have opened using the following code:

ximpMY = txtimpmy.Value
WorkbookImpromp = "c:/Deferred Revenue_" & ximpMY & ".xls"
Workbooks.Open Filename:=WorkbookImpromp

I would now like to activate it. I have tried using the following code:

Workbooks(workbookimpromp).Activate

When I try to use this code I get the following runtime error:

"Subcript out of range"

The only way I can get this to work is if I replace the variable workbookimpromp with "Deferred Revenue_" & ximpMY & ".xls"

Can anybody help me with this? I really would like to use the variable.

thanks

 

[SkipVought]

khansen97,

Most of the time Activate and Select are both UNNECESSARY and UNDESIREABLE except to determine what you want to be displayed once the procedure ends. faq707-4105 How Can I Make My Code Run Faster?

Here's my suggestion, if you must activate...

Dim wb1 as Workbook
ximpMY = txtimpmy.Value
WorkbookImpromp = "c:/Deferred Revenue_" & ximpMY & ".xls"
Set wb1 = Workbooks.Open Filename:=WorkbookImpromp
wb1.activate
...
Set wb1 = Nothing

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[khansen97]

When I try to put the "Set W1" code in I keep getting an "Expected end of statement error

 

[SkipVought]

sorry

Set wb1 = Workbooks.Open(Filename:=WorkbookImpromp)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884