Macro that saves two worksheets from a workbook and save them as a new workbook

[BenScadden]

I would greatly appreciate a macro that can be used, on a daily basis, to copy two sheets from a workbook (as values), save them as a new workbook to a specific location and name them with the current date.

My workbook is called 'Daily Cash Management'. The two pages I would like to copy from this are called 'NW Bank Bal' & 'Other Bank Bal'.

I would like them to be saved to following path 'S:\Cash Management\Cash Management Sheets\Daily Sheets\DCM Bank Bal Pages

Ideally the new file will be named with the full date for the day in question. So for today '25062008' - in English date format.

I intend on using this every day to save these two sheets so that I will have a copy of them for each day.

I am using Excel 2002.

Many thanks in advance.

 

[venkat1926]

park this type of a macro in the work book "cash management"

Option Explicit
Sub test()


Sheets(Array("Sheet1", "Sheet2")).Copy
ActiveWorkbook.SaveAs ("e:\excel\new")

End Sub

in such simple cases you start a macro and do the steps and view the macro and you can generalise it. This will be a good exercise.

 

[xenou]

Building on venkat's code a little, and adding a little error-checking:

Sub test()
Const strWS1 As String = "NW Bank Bal"
Const strWS2 As String = "Other Bank Bal"
Const strPath As String = "S:\Cash Management\Cash Management Sheets\Daily Sheets\DCM Bank Bal Pages"

'Date for new file name
strPath = strPath & "\" & Format(Now, "ddmmyyyy") & ".xls" '2003 Excel Sheet
Debug.Print strPath

'Copy sheets
Sheets(Array(strWS1, strWS2)).Copy

'Save workbook
If MyFileExists(strPath) Then
    MsgBox "File exists.  This routine will not overwrite the existing file.  Execution aborted."
Else
    ActiveWorkbook.SaveAs strPath
End If

End Sub
'---------------------------------------------------------
Public Function MyFileExists(strPath As String) As Boolean

If Dir(strPath) > "" Then
    MyFileExists = True
Else
    MyFileExists = False
    MsgBox strPath & " already exists."
End If

End Function

I wasn't sure if you want the date appended to the existing file name or just use the date by itself as the file name - above the latter course is followed. This can be easily changed.

Regards


Edit: Note I very rarely used excel 2002 but I think the file extension is the same as for 2003, i.e. ".xls" - correct? AB