Run Time Error 9

  • Thread starter Thread starter Gerard Goodland
  • Start date Start date
G

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
 
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
 
Back
Top