How can i assign a macro to select a particular Cell (which contains
a name) - which, when a "save" button on the sheet is selected, will save a
copy of that page under a different name - so that the original remains
un-affected.
I need this for a web input page
naturally i don't want the input page altered, but i want the input data
stored.

I have created this page using an excel web form.

Regards

Sub Make_New_Book()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 12 Aug 2008 09:59:08 -0700, EngelseBoer
wrote:

How can i assign a macro to select a particular Cell (which contains
a name) - which, when a "save" button on the sheet is selected, will save a
copy of that page under a different name - so that the original remains
un-affected.
I need this for a web input page
naturally i don't want the input page altered, but i want the input data
stored.

I have created this page using an excel web form.

Regards

Thanks Gord..
ok that is doing something...
though it started comeing up with a run error after a few attempts
debugger highlighted...
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
yet it has been createing new workbooks..
book 2
book 3
etc (as i am testing it in a file named book 1)

though the first time i ran it, i had the Cell containing the name selected
and it saved it as that name ie TEST.xls
other times i did not have that cell selected
and it created consecutive book

I recorded a new macro - to 1st select that cell, then run your macro and end

to get the macro to run though, I have to open the macro and command it to
run..
via Tools - macro - macros - run macro.....
how do i assign it to the "save" (commandbutton)

Thanks for your help

"Gord Dibben" wrote:

Sub Make_New_Book()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 12 Aug 2008 09:59:08 -0700, EngelseBoer
wrote:

How can i assign a macro to select a particular Cell (which contains
a name) - which, when a "save" button on the sheet is selected, will save a
copy of that page under a different name - so that the original remains
un-affected.
I need this for a web input page
naturally i don't want the input page altered, but i want the input data
stored.

I have created this page using an excel web form.

Regards

Open Forms Toolbar and select the "button" creator.

Draw a button on the sheet then right-clcik and "assign macro"

Also you can "edit text" to give your button a descriptive name.

As far as selecting a hard-coded cell before running the saveas routine
goes, the macro will overwrite the previous workbook if that cell text has
not changed since last run.

Do you want that to occur?


Gord

On Tue, 12 Aug 2008 14:38:11 -0700, EngelseBoer
wrote:

Thanks Gord..
ok that is doing something...
though it started comeing up with a run error after a few attempts
debugger highlighted...
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
yet it has been createing new workbooks..
book 2
book 3
etc (as i am testing it in a file named book 1)

though the first time i ran it, i had the Cell containing the name selected
and it saved it as that name ie TEST.xls
other times i did not have that cell selected
and it created consecutive book

I recorded a new macro - to 1st select that cell, then run your macro and end

to get the macro to run though, I have to open the macro and command it to
run..
via Tools - macro - macros - run macro.....
how do i assign it to the "save" (commandbutton)

Thanks for your help

"Gord Dibben" wrote:

Sub Make_New_Book()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 12 Aug 2008 09:59:08 -0700, EngelseBoer
wrote:

How can i assign a macro to select a particular Cell (which contains
a name) - which, when a "save" button on the sheet is selected, will save a
copy of that page under a different name - so that the original remains
un-affected.
I need this for a web input page
naturally i don't want the input page altered, but i want the input data
stored.

I have created this page using an excel web form.

Regards

The trial runs i have done have not caused it to overwrite the cell nor the
file or macro.
With the aid of a recorder macro to go select that cell 1st, then run the
script you provided, simply creates a ne file with the new name
if that name already exists it creates a "same file name 2" then 3 etc

but no... it must never overwrite anything
just cause a new file to be saved with the new data

Thanks for the help

Regards
Malcolm

"Gord Dibben" wrote:

Open Forms Toolbar and select the "button" creator.

Draw a button on the sheet then right-clcik and "assign macro"

Also you can "edit text" to give your button a descriptive name.

As far as selecting a hard-coded cell before running the saveas routine
goes, the macro will overwrite the previous workbook if that cell text has
not changed since last run.

Do you want that to occur?


Gord

On Tue, 12 Aug 2008 14:38:11 -0700, EngelseBoer
wrote:

Thanks Gord..
ok that is doing something...
though it started comeing up with a run error after a few attempts
debugger highlighted...
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
yet it has been createing new workbooks..
book 2
book 3
etc (as i am testing it in a file named book 1)

though the first time i ran it, i had the Cell containing the name selected
and it saved it as that name ie TEST.xls
other times i did not have that cell selected
and it created consecutive book

I recorded a new macro - to 1st select that cell, then run your macro and end

to get the macro to run though, I have to open the macro and command it to
run..
via Tools - macro - macros - run macro.....
how do i assign it to the "save" (commandbutton)

Thanks for your help

"Gord Dibben" wrote:

Sub Make_New_Book()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & ActiveCell.Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 12 Aug 2008 09:59:08 -0700, EngelseBoer
wrote:

How can i assign a macro to select a particular Cell (which contains
a name) - which, when a "save" button on the sheet is selected, will save a
copy of that page under a different name - so that the original remains
un-affected.
I need this for a web input page
naturally i don't want the input page altered, but i want the input data
stored.

I have created this page using an excel web form.

Regards

The code I provided will not create "same file name 2", then 3 etc.

Application.DisplayAlerts = False will cause an overwrite with no warning.

You must have altered the code.


Gord

On Tue, 12 Aug 2008 16:22:00 -0700, EngelseBoer
wrote:

simply creates a ne file with the new name
if that name already exists it creates a "same file name 2" then 3 etc