Saving a worksheet to a new workbook
 

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

Saving a worksheet to a new workbook
 

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

regards
FSt1

rega
" wrote:

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

Saving a worksheet to a new workbook
 

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

" wrote:

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

--

Dave Peterson

Saving a worksheet to a new workbook
 

On Feb 4, 5:21*pm, FSt1 wrote:
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

Saving a worksheet to a new workbook
 

On Feb 4, 5:25*pm, Dave Peterson wrote:
Worksheets("Source_Worksheet").Copy 'to a new workbook
activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls"
Workbooks("Source_Workbook.xls").Close

" wrote:

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

--

Dave Peterson

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

Saving a worksheet to a new workbook
 

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



" wrote:

On Feb 4, 5:25 pm, Dave Peterson wrote:
Worksheets("Source_Worksheet").Copy 'to a new workbook
activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls"
Workbooks("Source_Workbook.xls").Close

<<snipped

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

Saving a worksheet to a new workbook
 

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

On Feb 4, 6:25*pm, Dave Peterson wrote:
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