I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

Probably need to:

Sub dj()
newsht = ActiveSheet.Copy
Set Wkb = ActiveWorkbook
Wkb.Sheets("Sheet1").Name = "mySheet"
ActiveWorkbook.SaveAs Filename:="testfile.xls"
End Sub

You can modify to suit your purposes. This will automatically create a new
workbook when you copy the sheet without specifying a before or after
destination. Then you simply name the sheet if desired and save the workbook
as whatever path and file name you desire.

"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

hi
here is a personal macro i wrote some time back. saves a selected range from
an entire sheet(select the sheet) to a single cell (you get a warning if
saving a single cell). adjust to suit. work in 97 to 03. untested in 07.
Sub mac1SaveRange()
'Macro written by FSt1 4/27/98
Dim cnt As Long
Dim cell As Range
MsgBox "You have selected range" & Selection.Address
If Selection.Cells.Count = 1 Then
If MsgBox("You have selected only one cell. Continue?????", vbYesNo,
"Warning") = vbNo Then
Exit Sub
End If
End If
cnt = 0
For Each cell In Selection
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data in the selected range. Continue?!?!?!?!?",
vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
'ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
End Sub

warning: some lines wraped.

Regards
FSt1

"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

I missed the part about the dialog box to select a folder. This is a
modified version.
Sub dj()
newsht = ActiveSheet.Copy
Set Wkb = ActiveWorkbook
Wkb.Sheets("Sheet1").Name = "mySheet"
newFile = Application.Dialogs(xlDialogSaveAs).Show
Set Wkb = Nothing
ActiveWorkbook.Close
End Sub



"JLGWhiz" wrote:

Probably need to:

Sub dj()
newsht = ActiveSheet.Copy
Set Wkb = ActiveWorkbook
Wkb.Sheets("Sheet1").Name = "mySheet"
ActiveWorkbook.SaveAs Filename:="testfile.xls"
End Sub

You can modify to suit your purposes. This will automatically create a new
workbook when you copy the sheet without specifying a before or after
destination. Then you simply name the sheet if desired and save the workbook
as whatever path and file name you desire.

"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

Thanks JLGWhiz, it works fine in my application but has one niggly problem.
When I open the saved worksheet in the new folder and everytime I
try to close it (without changing anything) it asks me to save changes. Not
a big problem but can this be fixed? or is it "normal"
I have not tried the code from FSt1 yet, but it looks good for another
application I am working on.....Thanks guys for your help. Have a happy and
prosperous new year.
--
ypukpete


"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

Thanks JLGWhiz, it works a treat. Just one niggly problem. When I open the
saved workbook in its new folder and then exit, I am asked to save the
changes even though I have not changed anything. Is this a problem or just
normal?
I have not tried the code from FSt1 but I can use it in another application
I am working on. Thanks for your help guys. Have a happy and prosperous new
year.
--
ypukpete


"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

Thanks JLGWhiz, it works a treat. Just one niggly problem. When I open the
saved workbook in its new folder and then exit, I am asked to save the
changes even though I have not changed anything. Is this a problem or just
normal?
I have not tried the code from FSt1 but I can use it in another application
I am working on. Thanks for your help guys. Have a happy and prosperous new
year.
--

--
ypukpete


"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

Thanks JLGWhiz, it works a treat. Just one niggly problem. When I open the
saved workbook in its new folder and then exit, I am asked to save the
changes even though I have not changed anything. Is this a problem or just
normal?
I have not tried the code from FSt1 but I can use it in another application
I am working on. Thanks for your help guys. Have a happy and prosperous new
year.
--

--
ypukpete


"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

Thanks JLGWhiz, it works a treat. Just one niggly problem. When I open the
saved workbook in its new folder and then exit, I am asked to save the
changes even though I have not changed anything. Is this a problem or just
normal?
I have not tried the code from FSt1 but I can use it in another application
I am working on. Thanks for your help guys. Have a happy and prosperous new
year.
--

--
ypukpete


"ypukpete" wrote:

I want to save only the active worksheet in a workbook using a command button.
I need the Save As screen to appear so that I can send it to the folder I
require at the time and also give the worksheet a new name, but let me know
if it is invalid. Probably easy when you know how.
I am using excel 2000. Any assistance appreciated
--
ypukpete

*** Sent via Developersdex http://www.developersdex.com ***