The code
If Worksheets("cover sheet").Range("H7") < "" And Worksheets("cover
sheet").Range("O10") < "" And Worksheets("cover sheet").Range("E11") < ""
Then
If Len(Dir(mypath & myname & ".xls")) = 0 Then
Application.DisplayAlerts = False
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=mypath & myname & ".xls"
Windows("Script with Macros.xls").Activate
Cells.Select
Selection.Copy
Windows(myname & ".xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets.Add After:=Sheets(Sheets.Count)
Windows("Script with Macros.xls").Activate
Sheets("DIST " & mq).Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows(myname & ".xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Dist 17, 67, 196"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Cover Sheet"
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Cover Sheet").Select
Application.DisplayAlerts = True
End If
End If

Halts just after executing:

Sheets("DIST " & mq).Select

and produces the runtime 1004 error.

I'm having difficulty understanding why.

Any thoughts?

1004 is subscript out of range and generally it means that you are trying to
access something that does not exist. For example you can not select Cell A0
since the first row of a spreadsheet is row 1. In your case you are trying to
select a sheet that does not exist in the currently active workbook. In the
code take note of which workbook is active when the code executes and ensure
that the name of the worksheet is exactly the same as the name you are
referencing. Spelling and blank spaces count so double check what you have...
--
HTH...

Jim Thomlinson


"~L" wrote:

The code
If Worksheets("cover sheet").Range("H7") < "" And Worksheets("cover
sheet").Range("O10") < "" And Worksheets("cover sheet").Range("E11") < ""
Then
If Len(Dir(mypath & myname & ".xls")) = 0 Then
Application.DisplayAlerts = False
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=mypath & myname & ".xls"
Windows("Script with Macros.xls").Activate
Cells.Select
Selection.Copy
Windows(myname & ".xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets.Add After:=Sheets(Sheets.Count)
Windows("Script with Macros.xls").Activate
Sheets("DIST " & mq).Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows(myname & ".xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Dist 17, 67, 196"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Cover Sheet"
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Cover Sheet").Select
Application.DisplayAlerts = True
End If
End If

Halts just after executing:

Sheets("DIST " & mq).Select

and produces the runtime 1004 error.

I'm having difficulty understanding why.

Any thoughts?