help please..... can this be done
 

Hi thanks for taking the time to read this even if it cant be done ...
i have a template that opens up after incerting relavent info i need a macro
that can save the work book taking the file name from 2 cells (H13-E12) and
also be un editable then add 1 to the number in cell E12 on the origanal
template clear all data just entered and save as a new template over writing
the old one, the aim is to create a new and unchangeable number in cell E12
each time the the workbook is saved. and need this to work from a button in
the work sheet ....first off is this possible if so can
anyone help

thanx :)

help please..... can this be done
 

Quinn,

I am fairly sure that it can be done.

Play with the macro recorder to to get basic code for all the steps needed.
(use a copy of your template)

You can create a file name from the cells and save with that name:

Dim fname as string
fname = Range("H13") & " " & Range("E12")

' remove & " " if you don't want a space.

than add 1 to Range("E12"), clear all the data and resave
as the original file name.
Range("E12") = Range("E12") + 1

Range("......").Clearcontents

Hope this gets you started.

Post back and let us know how it works.
Also let us know if you need more specific code.

--
steveB

Remove "AYN" from email to respond
"QuinnDexter" wrote in message
...
Hi thanks for taking the time to read this even if it cant be done ...
i have a template that opens up after incerting relavent info i need a
macro
that can save the work book taking the file name from 2 cells (H13-E12)
and
also be un editable then add 1 to the number in cell E12 on the origanal
template clear all data just entered and save as a new template over
writing
the old one, the aim is to create a new and unchangeable number in cell
E12
each time the the workbook is saved. and need this to work from a button
in
the work sheet ....first off is this possible if so can
anyone help

thanx :)

help please..... can this be done
 

You use the term templace, but is the template a .xlt file in the template
directory or is it just a formatted workbook.

If the latter, when you say the orginal template, are there two workbooks or
only one. If two, how did the second get created. Or are you really saying
you want to do some SaveAs commands?

Assume 1 workbook with saveas

Public Button_click()
Dim rng1 as Range, rng2 as Range
Dim sName as String
With thisworkbook
sName = .FullName
set rng1 = .Worksheets("sheet1").Range("H13")
set rng2 = .Worksheets("Sheet1").Range("E12")
.SaveAs .Path & "\" &
rng1.Value & rng2.Value & ".xls", xlWorkbookNormal
.worksheets("Sheet1").UnProtect Password:="ABCD"
.Worksheets("Sheet1").Range("A1,B9,C10:C13,F21,M1: M30") _
.ClearContents
rng2 = rng2 + 1
.Worksheets("Sheet1").Protect Password:="ABCD"
Application.DisplayAlerts = False
.SaveAs .FullName
Application.DisplayAlerts = True
End With
End sub

Adjust to suit your purposes. Assumes E12 is Locked.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

"QuinnDexter" wrote in message
...
Hi thanks for taking the time to read this even if it cant be done ...
i have a template that opens up after incerting relavent info i need a
macro
that can save the work book taking the file name from 2 cells (H13-E12)
and
also be un editable then add 1 to the number in cell E12 on the origanal
template clear all data just entered and save as a new template over
writing
the old one, the aim is to create a new and unchangeable number in cell
E12
each time the the workbook is saved. and need this to work from a button
in
the work sheet ....first off is this possible if so can
anyone help

thanx :)

help please..... can this be done
 

Thanks steve... i will have a play with your sugestions.. can you tell me how
to bind it to the button i have placed on the work sheet. also can i protect
the saved copy so it cant be edited, and the number in the origanal template
must be protected from editing except when the 1 is added at the save

Tom... it is a template (though not saved in the template directory (saved
to desk top)) i say the origanal as it will creat another copy with a new
name and data when i save it,
thanks for your code i will give it a try and let you know

thanks :)

help please..... can this be done
 

Quinn,

First it helps if you reply to each post and leave the previous posts in.
This way we can more easily follow what has been said and suggested.
Thanks...

Not sure what you mean by "can you tell me how
to bind it to the button i have placed on the work sheet. also can i protect
the saved copy so it cant be edited"

You can use code to protect the workbook and the worksheets. Before you
save it - you can add a password to the VB code to prevent users from
accessing the code.

"If you can dream of it - you can usually build it in."

Just remember that password protection only prevents the user from making
unwanted entries and such. It can be easily broken.
The VB protection is a little stronger.

You can send me a copy of the template with explicit instructions and I can
work on it with you.


Remove "AYN" from email to respond

================================================== ================
My Previous post:
Quinn,

I am fairly sure that it can be done.

Play with the macro recorder to to get basic code for all the steps needed.
(use a copy of your template)

You can create a file name from the cells and save with that name:

Dim fname as string
fname = Range("H13") & " " & Range("E12")

' remove & " " if you don't want a space.

than add 1 to Range("E12"), clear all the data and resave
as the original file name.
Range("E12") = Range("E12") + 1

Range("......").Clearcontents

Hope this gets you started.

Post back and let us know how it works.
Also let us know if you need more specific code.


--
steveB

Remove "AYN" from email to respond
"QuinnDexter" wrote in message
...
Thanks steve... i will have a play with your sugestions.. can you tell me
how
to bind it to the button i have placed on the work sheet. also can i
protect
the saved copy so it cant be edited, and the number in the origanal
template
must be protected from editing except when the 1 is added at the save

Tom... it is a template (though not saved in the template directory (saved
to desk top)) i say the origanal as it will creat another copy with a new
name and data when i save it,
thanks for your code i will give it a try and let you know

thanks :)