saving in sequential numbering i.e 1,2 3 etc or by date/time?

S

stevehorton

Hi there,

Currently i have a vb statement that will copy a sheet and then save i
to a certain location.

what im interested to do is to set it up so when i click the button th
first is called 1, then 2 etc.

Also is it possible to save them as the current date and time??

my current code is below.

Sub savings()
'

Application.DisplayAlerts = False
ActiveSheet.copy
ActiveWorkbook.SaveAs Filename:="C:\SCH\1.xls", FileFormat:=xlNormal
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
_
CreateBackup:=False
ActiveWorkbook.Close
'

End Su
 
F

Freemini

One way is to add the following to your macro

filename1 = "C:\SCH\1" & Range("a1") & Range("b1") & Range("c1")
".xls"

and change the next line to
ActiveWorkbook.SaveAs Filename:=filename1, FileFormat ..........

with the dim statement at the beginning
Dim filename1 as String

you can then enter any numbers etc in cells A1, B1 and C1 of th
worksheet. If you enter the date in say C1 with a leading apostrophe i
can also be incorporated, but note the date must NOT be in any forma
using /

hth

Mik
 
R

Rollin_Again

What you want to do is possible by using the built in *Date* and *Time
functions in VBA but you will have to replace certain characters in th
filename since they are not allowed to be used in filenames by Windows
In the Date you will have to replace the */* character and in the tim
you will have to replace the *:* character. In my example, I hav
chosen to replace these characters with the *-* symbol and I hav
seperated the three components (filename, date, and time) wit
underscores *__*

ACTIVEWORKBOOK.SAVEAS FILENAME:=\"C:\SCH\\" & 1 & \"__\"
REPLACE(DATE, \"/\", \"-\") & \"__\" & REPLACE(FORMAT(TIME, \"HH:M
AM/PM\"), \":\", \"-\") & \".XLS\", FILEFORMAT:=XLNORMAL, _
PASSWORD:=\"\", WRITERESPASSWORD:=\"\", READONLYRECOMMENDED:=FALSE, _
CREATEBACKUP:=FALS


The above code will generate the following filename:

[/b]1__7-22-2004__09-30 AM.xls[/b
 
Top