Trying to close Excel file nut get error msg
 

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...

Trying to close Excel file nut get error msg
 

On Aug 25, 9:16 am, LetMeDoIt wrote:
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?

Trying to close Excel file nut get error msg
 

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

On Aug 25, 3:40 pm, Ferris wrote:
On Aug 25, 9:16 am, LetMeDoIt wrote:





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?- Hide quoted text -

- Show quoted text -

Trying to close Excel file nut get error msg
 

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

"LetMeDoIt" wrote:

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

On Aug 25, 3:40 pm, Ferris wrote:
On Aug 25, 9:16 am, LetMeDoIt wrote:





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?- Hide quoted text -

- Show quoted text -

Trying to close Excel file nut get error msg
 

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

On Aug 25, 5:14 pm, JLGWhiz wrote:
I am using XP Office 2003 and it ran OK on mine.



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

On Aug 25, 3:40 pm, Ferris wrote:
On Aug 25, 9:16 am, LetMeDoIt wrote:

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -