"Save a Worksheet as File??"

  • Thread starter Thread starter Don Ghylin
  • Start date Start date
D

Don Ghylin

I've got a Workbook that contains a main data sheet
(Data) along with a number of other sheets. One of these
sheets (Activation Invoice) calls data from the the main
(Data) sheet. I'm able to save this Activation Invoice as
a seperate file using a Macro to copy the sheet to a new
book and then saving it. The problem is I have to go
into Explore and change the file name everytime this
Invoice is generated. Is there a way to automatically
name the file, within the Macro, with a date progressed
name....i.e...Jan-1-04....Jan-16-04...I can change the
file name format if necessary but as you can see, the
file is generated twice monthly.

Thanks to all, you're a great help here, I do enjoy
reading all the posts on this forum.

Don
 
Why don't you post your existing macro? It would be a lot easier then to
give you the couple of extra lines needed.
 
Since we didn't see your code, this might help to move and/or rename file.
Try it.

Sub moveandorrenamefile()
OldName = "C:\oldfolderl\oldname.xls"
NewName = "C:\newfolder\newname.xls"
Name OldName As NewName
End Sub
 
Sorry Vasant....wasn't sure if I should post the code or
not but since you asked.....here you go.....:)

Sub Sort_Date_Save()
'
' Sort_Date_Save Macro
' Macro recorded 5/30/2004 by Don Ghylin
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Data").Select
Application.Goto Reference:="R3C2:R500C120"
Selection.Sort Key1:=Range("B3"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("B3").Select
Sheets("Activation Invoice").Select
Range("L6").Select
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("Activation Invoice").Copy
Range("L6").Select
Selection.ClearContents
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Don\My
Documents\WirelessLedger\Test1.xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet2").Select
End Sub

The first thing this code does is sort the Data sheet,
then goes to the Invoice sheet, copies this sheet to a
new Workbook, copies the date (from a cell with Today()
function in it), pastes special (value) in the
appropriate cell (that locks the date of the Invoice),
and then saves that Sheet..actually saves that entire
Book, but that's all that will ever be in that file.

Thanks for the quick response guys/gals....hope this
clarifies what I'm trying to do here.

Don
 
So do you want to give the new workbook a name other than "test1.xls"? Do
you want to give it the name of the date on which you perform this
operation? In that case, try:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Don\My Documents\WirelessLedger\" _
& Format(Date, "d-mmm-yyyy") & ".xls"
 
EXACTLY....:)...works like a charm, thank you very much
Vasant. I tried what you posted, then changed it to "mmm-
d-yyyy" and it worked great. Now, if I can figure out
how to get "May" to come out "05" in the file name, it
would alphabetize correctly in Explore, but that's not
really necessary.

Thanks again for the great help, much appreciated my
friend,

Don
 
Again, my hat is off to you with many thanks Vasant.
That works great and allows Explore to alphabetize that
folder very nicely.

Don
 
Back
Top