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

if i did -- i have no clue how i did
it was saving book 1 then 2 etc
though i never tested it on an actual name in said cells, that was happening
before i recorded a macro to to go select that cell b4 running your code

i am too dumb at this to modify your code :)
but as per my previous mail -- it sure dont do it now -- go figure

PC programmes is wiered -- white-mans magic --LOL



"Gord Dibben" wrote:

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

umm
previous message didnt send...

so here goes again...

Right clicking on the button doesnt give me the option "assign macro"
so i selected "view code " instead and it gave me a new start and end sub
so i merged the other 2 codes into it.

What i run now is the script below -- where ("D1:F2"). are merged and
centered cells containing the new input

and um no -- now it is not creating a "same name 2 -- 3 -- 4" etc file now
though it was creating book 1 2 3 etc befor i recorded a macro to
select those cells 1st, then run your script.

Is it possible to "create same_name 2...3...4" if name already exists ?
though i dont think i will encounter the name being entered 2x in my
application

see new script below
regards


Private Sub CommandButton1_Click()
'
' Macro3 Macro
' Macro recorded 12/08/2008 by Malcolm
'

'
Range("D1:F2").Select
Application.Run "Book1.xls!Make_New_Book"
End Sub
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" wrote:

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

maybe it was saving book 2 3 4 etc becuase the "active cell" was blank --
where i had left the curser - hence the need to record a makro to go select
the requred ceel 1st - making it the active cell which had data

regards


"Gord Dibben" wrote:

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

First............you don't need the extra macro to select a cell.

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

Second.................you didn't see "Assign Macro" because you used a
button from the Control Toolbox, not the Forms Toolbar.

Third....................I don't know how to make the filenames increment
from 1 to 2 to 3 etc.

I'll have to wait for someone with more skills to jump in please.


Gord

On Tue, 12 Aug 2008 17:10:37 -0700, EngelseBoer
wrote:

umm
previous message didnt send...

so here goes again...

Right clicking on the button doesnt give me the option "assign macro"
so i selected "view code " instead and it gave me a new start and end sub
so i merged the other 2 codes into it.

What i run now is the script below -- where ("D1:F2"). are merged and
centered cells containing the new input

and um no -- now it is not creating a "same name 2 -- 3 -- 4" etc file now
though it was creating book 1 2 3 etc befor i recorded a macro to
select those cells 1st, then run your script.

Is it possible to "create same_name 2...3...4" if name already exists ?
though i dont think i will encounter the name being entered 2x in my
application

see new script below
regards


Private Sub CommandButton1_Click()
'
' Macro3 Macro
' Macro recorded 12/08/2008 by Malcolm
'

'
Range("D1:F2").Select
Application.Run "Book1.xls!Make_New_Book"
End Sub
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" wrote:

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