Trying to save a worksheet

[Guest]

Help!!!

I recorded a macro to save as a worksheet in a workbook. I get the save as dialog box. What I want to do is once my data is populated in the worksheet to click my button (i want to attach my macro to )
save a new worksheet to the same directory every time with a different name like a date.
for example
c:\temp\07152004

And also not show the save as box I don't want to have to click any button just look in the temp directory and see file names.
is this doable?

 

[Frank Kabel]

Hi
try something like

sub save_it
dim fname
dim fpath
fpath="c:\temp\"
fname=Format(now,"MMDDYYYY_hhmmss")
activeworkbook.saveas fpath & fname
end sub

 

[Guest]

Frank you are awesome. This is what I wanted.
Thank you
Ptree
Chicago, Illinois

 

[David McRitchie]

I would modify Frank's macro to put the year in front as
in YYYYMMDD_hhmmss in your backup.
That would make sure you know when you created it rather than
just going by what you see in the directory which is usually the
date modified.

 

[David McRitchie]

Hi Ptree,
Please avoid changing the subject. It makes for confusion, because
it looks like it is in a separate thread. In Outlook Express we will see
replies below ours in RED. Since you posted through Communities,
I don't know what you see but you are able to identify replies to your
questions in some marked manner. Even if you supply the thankyou
to a different subthread chances are everyone in the thread will have
seen it.

When a Thank You is really important in a thread is when you get
divergent answers not replies that enhance each other but
completely different interpretations of the problem or different ways
of solving the same problem -- that is when the Thank You indicates
which solution is was actually used and why.

 

[kennected]

Works great for me Frank but there's always something more, right? Can I
make the contents of a cell a prefix to the date code. In my case the
contents of cell c15 of the original workbook is a customer core that we put
in. This work help us sort saved files by customer when necessary.

 

[Dave Peterson]

Option explicit
Sub SaveIt2()

dim fName as string
dim fPath as string

fpath = "c:\temp\"

with activeworkbook
fname = .worksheets("sheet999").range("c15").value _
& format(now, "mmddyyyy_hhmmss") & ".xls"
.saveas filename:=fpath & fname, fileformat:=xlworkbooknormal
end with

End Sub