Hi,

I want to save a worksheet in a workbook as a separate excel file containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks

Remove the cancel test

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
Hi,

I want to save a worksheet in a workbook as a separate excel file
containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks

That's what I had before but it always asks to save the new file twice. Also
if I want to cancel the save it just keeps looping which is why I added the
last bit:

Or msoButtonSetOkCancel = Cancel.

Any more suggestions?

"Bob Phillips" wrote:

Remove the cancel test

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
Hi,

I want to save a worksheet in a workbook as a separate excel file
containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks

You need to test fname after the loop to decide what action to take. I have
no idea why it asks to save it twice, you don't show that code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
That's what I had before but it always asks to save the new file twice.
Also
if I want to cancel the save it just keeps looping which is why I added
the
last bit:

Or msoButtonSetOkCancel = Cancel.

Any more suggestions?

"Bob Phillips" wrote:

Remove the cancel test

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
Hi,

I want to save a worksheet in a workbook as a separate excel file
containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks

This is the complete macro code:

Sub SaveAs()

dim wsoutput as worksheet
Set wsOutput = Worksheets("output")

wsOutput.Select
wsOutput.Copy

Cells.Select
Range("K1").Activate
ActiveWorkbook.Colors(53) = RGB(247, 252, 255)
Range("K1").Select

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False 'Or msoButtonSetOkCancel = Cancel

ActiveWindow.Close

End Sub

"Bob Phillips" wrote:

You need to test fname after the loop to decide what action to take. I have
no idea why it asks to save it twice, you don't show that code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
That's what I had before but it always asks to save the new file twice.
Also
if I want to cancel the save it just keeps looping which is why I added
the
last bit:

Or msoButtonSetOkCancel = Cancel.

Any more suggestions?

"Bob Phillips" wrote:

Remove the cancel test

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
Hi,

I want to save a worksheet in a workbook as a separate excel file
containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks

This should be what you want

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False

If fname < False Then
ActiveWorkbook.SaveAs fname
ActiveWorkbook.Close savechanges:=False
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
This is the complete macro code:

Sub SaveAs()

dim wsoutput as worksheet
Set wsOutput = Worksheets("output")

wsOutput.Select
wsOutput.Copy

Cells.Select
Range("K1").Activate
ActiveWorkbook.Colors(53) = RGB(247, 252, 255)
Range("K1").Select

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False 'Or msoButtonSetOkCancel = Cancel

ActiveWindow.Close

End Sub

"Bob Phillips" wrote:

You need to test fname after the loop to decide what action to take. I
have
no idea why it asks to save it twice, you don't show that code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
That's what I had before but it always asks to save the new file
twice.
Also
if I want to cancel the save it just keeps looping which is why I
added
the
last bit:

Or msoButtonSetOkCancel = Cancel.

Any more suggestions?

"Bob Phillips" wrote:

Remove the cancel test

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
Hi,

I want to save a worksheet in a workbook as a separate excel file
containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks

thanks for your help. I changed it abit and it works:

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False Or msoButtonSetCancel


"Bob Phillips" wrote:

This should be what you want

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False

If fname < False Then
ActiveWorkbook.SaveAs fname
ActiveWorkbook.Close savechanges:=False
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
This is the complete macro code:

Sub SaveAs()

dim wsoutput as worksheet
Set wsOutput = Worksheets("output")

wsOutput.Select
wsOutput.Copy

Cells.Select
Range("K1").Activate
ActiveWorkbook.Colors(53) = RGB(247, 252, 255)
Range("K1").Select

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False 'Or msoButtonSetOkCancel = Cancel

ActiveWindow.Close

End Sub

"Bob Phillips" wrote:

You need to test fname after the loop to decide what action to take. I
have
no idea why it asks to save it twice, you don't show that code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
That's what I had before but it always asks to save the new file
twice.
Also
if I want to cancel the save it just keeps looping which is why I
added
the
last bit:

Or msoButtonSetOkCancel = Cancel.

Any more suggestions?

"Bob Phillips" wrote:

Remove the cancel test

Do
fname = Application.GetSaveAsFilename
Loop Until fname < False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gina" wrote in message
...
Hi,

I want to save a worksheet in a workbook as a separate excel file
containing
that one sheet, when a button is clicked. The code i have is:

Do
fName = Application.GetSaveAsFilename
Loop Until fName < False Or msoButtonSetOkCancel = Cancel

But it doesn't seem to work properly. Can anyone help?

Thanks