Append current date to filename

[Rhonda]

How can I append the current date to a filename when I
save so I can have monthly backups?

Thanks in Advance

Rhonda

 

[Tom Ogilvy]

ThisWorkbook.SaveCopyAs Left(Thisworkbook.Fullname,Len( _
Thisworkbook.Fullname)-4) & format(date,"_yyyy_mm_dd") & ".xls"

 

[Rhonda]

Sorry, forgot this part, this is part of the function
here.



BackupFileName = awb.FullName
i = 0
While InStr(i + 1, BackupFileName, ".") > 0
i = InStr(i + 1, BackupFileName, ".")
Wend
If i > 0 Then BackupFileName = Left
(BackupFileName, i - 1)
BackupFileName = BackupFileName & ".bak"
OK = False
On Error GoTo NotAbleToSave

 

[Rhonda]

Thanks Tom, works perfect!!

I have a quick question though. My worksheet now updates
figures monthly and saves them in a backup spreadsheet by
month according to date. How would you suggest to merge
all figures from each backup into another speadsheet(not
the original) and store them for data analysis?

 

[Tom Ogilvy]

Look in the data menu at the consolidate function.

or do you mean copy a sheet from each workbook into a single workbook.

If so, are only the workbooks to have a sheet copied located in a single
directory. What is the sheet name?

 

[Rhonda]

Right now I am using one worksheet of a workbook that
saves a copy of the printer info for each month for
archiving. I was using the current date function and then
refreshing the data into the same columns and then saving
it at month end for backup.

What I need is to take the columns with the figures from
each monthly backup and place them in another workbook
worksheet. I don't need any functions. Ex. January I
have: Print counter copy meter etc
356,000 134
I have many other columns going across.
February I Haverint Counter copy meter
546,789 12

What I need is a way, that after each backup is saved, to
take these columns of data and just combine them all in a
workbook. These columns would have to be selected b/c I
don't need the first 6 columns b/c it's just labeling
data such as serial number, printer name etc. EX

Hydro Xerox Printer Database

Location IC# Customer Account Xerox Desc.
Bay D'Espoir 12314 954733150 DocuCenter

This info I will copy to the workbook. Then I want the
figures from the specified columns to appear in the rows
for each month(without human intervention to reduce
errors). So then I can do functions on the data such as
total printing for month of September. I tried to begin a
merge but it disables my macros and I really don't know
how that would work. Maybe I'm going about it in a
tedious way, if you have any other ideas as to how you
would go about it please let me know.

Thanks
Rhonda