Save Worksheet based on Form Selections and Replace existing copies

[ron2k]

Hi guys,

I've been working on a report generator using a form which has the following comboboxes:

cboRptname: Report Name (5 options here)
cboYear: Year of Report
cboMonth: Month of Report
cboDay: Day of the month

The text file being imported exists in different folders for each day.

Anyways, the import and all the formatting works good, now I'm trying to save the worksheet. The worksheet is named after the selection in cboRptname (or cboRptname.Value).

So I'd like something like:

Private Sub SaveWS()
    Dim wb As Workbook
    Worksheets("cboRptname.Value").Copy
    Set wb = ActiveWorkbook
    wb.SaveAs Filename:="C:\excel\" & cboYear.Value & "\Qtr4\" & cboRptname.Value & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls"
    wb.Close
End Sub

However, the directory in which it is going to be saved will depend on the cboMonth.Value, where:

cboMonth.Value = "01"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr4\Jan\"...
cboMonth.Value = "02"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr4\Feb\"...
cboMonth.Value = "03"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr4\Mar\"...
cboMonth.Value = "04"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr1\Apr\"...
cboMonth.Value = "05"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr1\May\"...
cboMonth.Value = "06"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr1\June\"...
cboMonth.Value = "07"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr2\Jul\"...
cboMonth.Value = "08"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr2\Aug\"...
cboMonth.Value = "09"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr2\Sep\"...
cboMonth.Value = "10"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr3\Oct\"...
cboMonth.Value = "11"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr3\Nov\"...
cboMonth.Value = "12"
Save path will be "C:\excel\ & cboYear.Value & "\Qtr3\Dec\"...

I was practicing with "Select Case", but I'm very crude in code skills, so you help will be very much appreciated.

Thanks,

EDIT: And if the file already exists in that directory, is it possible to instruct excel to replace it. And I'm using xl 2007 but want to save them as 2003 files.

Ron