Saving a worksheet to a new workbook

[dhgerstman]

I am trying to copy a worksheet to a new workbook and then close the
current workbook, so I can work in the worksheet.
This is what I've written.
Worksheets("Source_Worksheet").Activate
ActiveSheet.Copy
ActiveSheet.Paste
ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls"
Workbooks("Source_Workbook.xls").Close

When I try the SaveAs, I get a run-time 1004 error for a bad path or
file name. (wsname is a string data type.)

David

 

[FSt1]

hi
try this instead
replace the line
ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls"
with
Application.Dialogs(xlDialogSaveAs).Show

regards
FSt1

rega

 

[Dave Peterson]

Worksheets("Source_Worksheet").Copy 'to a new workbook
activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls"
Workbooks("Source_Workbook.xls").Close

 

[dhgerstman]

hi
try this instead
replace the line
ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls"
with
Application.Dialogs(xlDialogSaveAs).Show

regards
FSt1

rega

Rega,

That helped me identify a problem. Thank you.

David

 

[dhgerstman]

Worksheets("Source_Worksheet").Copy 'to a new workbook
activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls"
Workbooks("Source_Workbook.xls").Close

Dave,

Thanks for the suggestion. Unfortunately, for some reason I'm being
sent to:
c:\Program Files\Common Files\System\MSMAPI\1033

Why is that? And how can I redirect the file location. (Chdir and
Chdrive don't seem to be doing the trick)

Thanks,

David

 

[Dave Peterson]

First, I had a typo. Sorry about that!

activesheet.parent.saveas filEname:="c:\My Excel Files\" & wsname & ".xls"
(Missed an E in FileName:=...)

Do you have a C: drive on this pc?
Is there a folder named "\my Excel files\" on that C: drive?
What does the wsname variable hold?

====
And upon reflection, I'd be more explicit in the .saveas line:

activesheet.parent.saveas _
filename:="c:\My Excel Files\" & wsname & ".xls", _
fileformat:=xlworkbooknormal

 

[dhgerstman]

Dave,

Thanks a lot. First of all I was really stupid and copied your code
directly, not even noticing the missing "e." So thanks for that
correction. It looks like my mistake was using a spreadsheet that I'd
included in an e-mail, that's why I couldn't save things. I must have
used it once and kept on opening it via the "Documents" menu from the
Start button. So I propogated the mistake.

David