Can't activate an existing workbook

's Computer Specifications

I have a workbook open but not active named 123Q and a
Range("Input!ab2") = 123

Private Sub CommandButton2_Click() 'Generates PDF Quote Form

Dim CurrentPrinter As Variant
Dim quotenumber As String
Dim quotenumber1 As String

quotenumber = Range("Input!ab2")
quotenumber1 = quotenumber + "Q" + ".xls"

'Windows("quotenumber1").Activate

Why do i get an error message "subscript out of range"

oldjay

's Computer Specifications

Try changing from:

quotenumber = Range("Input!ab2")

To:

quotenumber = Worksheets("Input").Range("ab2")

"Oldjay" wrote:

> I have a workbook open but not active named 123Q and a
> Range("Input!ab2") = 123
>
> Private Sub CommandButton2_Click() 'Generates PDF Quote Form
>
> Dim CurrentPrinter As Variant
> Dim quotenumber As String
> Dim quotenumber1 As String
>
> quotenumber = Range("Input!ab2")
> quotenumber1 = quotenumber + "Q" + ".xls"
>
> 'Windows("quotenumber1").Activate
>
> Why do i get an error message "subscript out of range"
>
> oldjay
>
>

's Computer Specifications

On 23 Nov., 18:04, Oldjay <Old...@discussions.microsoft.com> wrote:
> I have a workbook open but not active named 123Q and a
> Range("Input!ab2") = 123
>
> Private Sub CommandButton2_Click() 'Generates PDF Quote Form
>
> Dim CurrentPrinter As Variant
> Dim quotenumber As String
> Dim quotenumber1 As String
>
> quotenumber = Range("Input!ab2")
> quotenumber1 = quotenumber + "Q" + ".xls"
>
> 'Windows("quotenumber1").Activate
>
> Why do i get an error message "subscript out of range"
>
> oldjay

Hi

Try this:

quotenumber=Workbook("123Q").Worksheets("Input").Range("AB2").Value

Regards,

Per

's Computer Specifications

Private Sub CommandButton2_Click() 'Generates PDF Quote Form

Dim CurrentPrinter As Variant
Dim quotenumber As String
Dim quotenumber1 As String

quotenumber = Worksheets("Input").Range("ab2")
quotenumber1 = quotenumber + "Q" + ".xls"

Windows("quotenumber1").Activate

Tried it but have the same result

"JLGWhiz" wrote:

> Try changing from:
>
> quotenumber = Range("Input!ab2")
>
> To:
>
> quotenumber = Worksheets("Input").Range("ab2")
>
>
>
> "Oldjay" wrote:
>
> > I have a workbook open but not active named 123Q and a
> > Range("Input!ab2") = 123
> >
> > Private Sub CommandButton2_Click() 'Generates PDF Quote Form
> >
> > Dim CurrentPrinter As Variant
> > Dim quotenumber As String
> > Dim quotenumber1 As String
> >
> > quotenumber = Range("Input!ab2")
> > quotenumber1 = quotenumber + "Q" + ".xls"
> >
> > 'Windows("quotenumber1").Activate
> >
> > Why do i get an error message "subscript out of range"
> >
> > oldjay
> >
> >

's Computer Specifications

Take the quotes off the last line:

Windows(quotenumber1).Activate

With that change it works for me in a regular module.

But I agree with the other posts that suggest a different way to reference
the range, although I don't think that's why you are seeing the error.

--
Tim Zych
SF, CA

"Oldjay" <(E-Mail Removed)> wrote in message
news:F84297F7-78AF-4EB6-9400-(E-Mail Removed)...
>I have a workbook open but not active named 123Q and a
> Range("Input!ab2") = 123
>
> Private Sub CommandButton2_Click() 'Generates PDF Quote Form
>
> Dim CurrentPrinter As Variant
> Dim quotenumber As String
> Dim quotenumber1 As String
>
> quotenumber = Range("Input!ab2")
> quotenumber1 = quotenumber + "Q" + ".xls"
>
> 'Windows("quotenumber1").Activate
>
> Why do i get an error message "subscript out of range"
>
> oldjay
>
>

's Computer Specifications

Can't use 123Q because the inactive workbook is variable.

"(E-Mail Removed)" wrote:

> On 23 Nov., 18:04, Oldjay <Old...@discussions.microsoft.com> wrote:
> > I have a workbook open but not active named 123Q and a
> > Range("Input!ab2") = 123
> >
> > Private Sub CommandButton2_Click() 'Generates PDF Quote Form
> >
> > Dim CurrentPrinter As Variant
> > Dim quotenumber As String
> > Dim quotenumber1 As String
> >
> > quotenumber = Range("Input!ab2")
> > quotenumber1 = quotenumber + "Q" + ".xls"
> >
> > 'Windows("quotenumber1").Activate
> >
> > Why do i get an error message "subscript out of range"
> >
> > oldjay
>
> Hi
>
> Try this:
>
> quotenumber=Workbook("123Q").Worksheets("Input").Range("AB2").Value
>
> Regards,
>
> Per
>

's Computer Specifications

Thanks That did the trick

"Tim Zych" wrote:

> Take the quotes off the last line:
>
> Windows(quotenumber1).Activate
>
> With that change it works for me in a regular module.
>
> But I agree with the other posts that suggest a different way to reference
> the range, although I don't think that's why you are seeing the error.
>
> --
> Tim Zych
> SF, CA
>
>
> "Oldjay" <(E-Mail Removed)> wrote in message
> news:F84297F7-78AF-4EB6-9400-(E-Mail Removed)...
> >I have a workbook open but not active named 123Q and a
> > Range("Input!ab2") = 123
> >
> > Private Sub CommandButton2_Click() 'Generates PDF Quote Form
> >
> > Dim CurrentPrinter As Variant
> > Dim quotenumber As String
> > Dim quotenumber1 As String
> >
> > quotenumber = Range("Input!ab2")
> > quotenumber1 = quotenumber + "Q" + ".xls"
> >
> > 'Windows("quotenumber1").Activate
> >
> > Why do i get an error message "subscript out of range"
> >
> > oldjay
> >
> >
>
>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I open an Excel Workbook from Outlook macro and activate that workbook?	Damil4real	Microsoft Excel Discussion	1	25th Jan 2011 04:56 AM
How to save existing Workbook with a new new name, without changingthe open Workbook name?	HammerJoe@gmail.com	Microsoft Excel Programming	7	3rd Feb 2008 03:35 AM
Workbook.Activate / Window.Activate problem	Tim	Microsoft Excel Programming	3	3rd Feb 2006 11:38 PM
Calling a Personal.XLS Sub from anther workbook's 'This Workbook' Sheet Activate	Jack Gillis	Microsoft Excel Discussion	2	21st Mar 2005 11:58 PM
when open workbook, change existing link to another workbook	Chrissie Frisbee	Microsoft Excel Misc	1	10th Sep 2003 12:57 AM