Trying to close Excel file nut get error msg

[LetMeDoIt]

Greetings,

I'm created a new Excel file on the fly and copying a specific
worksheet into that new file. This part is fine, until I try to close
that new file (see last file of code) and get the error msg:

Run time error 9
subscript out of range.

Here's the logic in the code:


newFname = "NewEXCELfile.XLS"
Set NewWks = Workbooks.Add(1).Worksheets(1)
With NewWks
.Parent.SaveAs FileName:=newFname, FileFormat:=xlWorkbookNormal
End With
NewWks.Name = "MyNewSheet"
Set destrange = NewWks.Range("A1:z100")
Set sourceRange = ThisWorkbook.Sheets("SHEET1").Range("A1:z100")
sourceRange.Copy
destrange.PasteSpecial xlPasteColumnWidths, , False, False
destrange.PasteSpecial xlPasteFormats, , False, False
destrange.PasteSpecial (xlPasteFormulas)
application.CutCopyMode = False
Workbooks(newFname).Close

Many thanks...

 

[Ferris]

Greetings,

I'm created a new Excel file on the fly and copying a specific
worksheet into that new file. This part is fine, until I try to close
that new file (see last file of code) and get the error msg:

Run time error 9
subscript out of range.

Here's the logic in the code:

newFname = "NewEXCELfile.XLS"
Set NewWks = Workbooks.Add(1).Worksheets(1)
With NewWks
.Parent.SaveAs FileName:=newFname, FileFormat:=xlWorkbookNormal
End With
NewWks.Name = "MyNewSheet"
Set destrange = NewWks.Range("A1:z100")
Set sourceRange = ThisWorkbook.Sheets("SHEET1").Range("A1:z100")
sourceRange.Copy
destrange.PasteSpecial xlPasteColumnWidths, , False, False
destrange.PasteSpecial xlPasteFormats, , False, False
destrange.PasteSpecial (xlPasteFormulas)
application.CutCopyMode = False
Workbooks(newFname).Close

Many thanks...

I was able to run your code without any errors... Is all of your code
in the list you provided? I'm using xl2007, what version are you
using?

 

[LetMeDoIt]

Thanks for looking into it. I'm using Office 2003 running XP on a
laptop. Maybe I should try this on windows 2000.

 

[Guest]

I am using XP Office 2003 and it ran OK on mine.

Thanks for looking into it. I'm using Office 2003 running XP on a
laptop. Maybe I should try this on windows 2000.

 

[LetMeDoIt]

I rewrote part of the code to handle the closing. Many thanks for
testing this.
regards.