Run Time Error 9

[Gerard Goodland]

Hi all,

When I run my macro the code below is the last part. The last line
before the "End with" is where it gives the " Run time error 9 Subscript
out of range".
Everything appears to work ok right until I try to save the file.

Thanks for the help.

Sheets("Inventory").Select
Sheets("Inventory").Copy

With ActiveWorkbook
.SaveAs Filename:=.Worksheets("sheet1").Range("x4").Value
End With

[Bernie Deitrick]

Gerard,

When you .Copy a worksheet object, it creates a new workbook, which becomes
the activeworkbook. But your copied sheet is named Invetory, so Sheet1
doesn't exist in the activeworkbook. Perhaps you could use something like:

Sheets("Inventory").Select
Sheets("Inventory").Copy
ActiveWorkbook.SaveAs _
Filename:=Thisworkbook.Worksheets("sheet1").Range("x4").Value

Or maybe you mean

Sheets("Inventory").Select
Sheets("Inventory").Copy
ActiveWorkbook.SaveAs _
Filename:=Activeworkbook.Worksheets(1).Range("x4").Value


or perhaps you could be more specific:

Sheets("Inventory").Select
Sheets("Inventory").Copy
ActiveWorkbook.SaveAs _
Filename:=Workbooks("Name.xls").Worksheets("sheet1").Range("x4").Value

HTH,
Bernie
MS Excel MVP


"Gerard Goodland" <ggoodland@ns.sympatico.ca> wrote in message
news:bDZEd.207701$Np3.8793930@ursa-nb00s0.nbnet.nb.ca...
> Hi all,
>
> When I run my macro the code below is the last part. The last line before
> the "End with" is where it gives the " Run time error 9 Subscript out of
> range".
> Everything appears to work ok right until I try to save the file.
>
> Thanks for the help.
>
> Sheets("Inventory").Select
> Sheets("Inventory").Copy
>
> With ActiveWorkbook
> .SaveAs Filename:=.Worksheets("sheet1").Range("x4").Value
> End With
>