Generate filename from 4 different cells 1

[jazminecat23]

Hi everyone - well, thanks to Skip and a few others, I'm almost there. The last thing that has me stymied is generating a filename, with a loop.

I have a function that loops through a set of values on sheet 1, plugs them into sheet 2, performs calculations, and then saves the workbook, and then goes to the next set of values, lather, rinse, repeat.

I'd like to generate the filename dynamically, which would save us the trouble of editing the values list every month. It will have about 50 lines, when all is said and done...

What I want is a filename in the format FEB07AUD5061. This is broken down as follows:

FEB - this is Sheet2!G4 converted to a month. Sheet2!G4 has a single number in it. So for this example, it would say "2".

07 - this is the last two digits of Sheet2!G2 - which in this case says "2007"

AUD - this is the first three characters of the item in my value list in column A, sheet 1.

5061 - this is the value in col c, sheet 1.

So for the following values:

COL A B C
Alpha 00001 510
Alpha 00001 520
Alpha 00001 540
Bravo 00001 560
Bravo 00001 590

I would like to generate the following file names, for example:

FEB07ALP510
FEB07ALP520
FEB07ALP540
FEB07BRA560
FEB07BRA590

Here's my code, which thanks to Skip, is working - the red part is what I'm working on now:

Private Sub CmdRunSpreadsheet_Click()
'
' 
' To insert values and create monthly budget distribution automatically

'Check for login to spreadsheet server
    If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then
    Exit Sub
    Else

'1. Unhide button, and all worksheets and rows
 
    cmdRunSpreadsheet.Visible = True
    Sheets("GXE Source").Visible = True
    Sheets("DistList").Visible = True
    
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = False
    
    
'2. Get values from Master Sheet
    Dim r As Range
    For Each r In Sheets("DistList").Range(Sheets("DistList").[A1], Sheets("DistList").[A1].End(xlDown))
      sDept = r.Value
      nVal1 = r.Offset(0, 1).Value
      nVal2 = r.Offset(0, 2).Value
      sName = r.Offset(0, 3).Value
    'BudgetStatus is where the processing occurs
      With Sheets("BudgetStatus")
         '.Cells(10, "A").Value = sDept
         .Cells(6, "G").Value = nVal1
         .Cells(7, "G").Value = nVal2
      End With
  
 '3. calculate
    Application.Calculate
    
 
 '4. Generate Detail Reports, and hide GXE sheet
    Application.Run ("ExpandDetailReports")
 
'5. Hide all extraneous rows on worksheet

    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
    
'6. Hide button, GXE sheet, and master values sheet.
 
    cmdRunSpreadsheet.Visible = False
    Sheets("GXE Source").Visible = False
    Sheets("DistList").Visible = False
    
'7. overwrite formulae with values, confirm first
        If MsgBox("Do you want to convert all formulas?", vbYesNo, "Remove Formulas?") = vbNo Then
        Exit Sub
        Else

        Cells.Select
        Range("A38").Activate
        Selection.Copy
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        ActiveWindow.ScrollRow = 1
   
        
        End If

[red]

SaveAs "C:\Documents and Settings\Administrator\Desktop" & "\" & sName & ".xls"

[/red]

Next

End If




End Sub

currently, in the saveas section, sName refers to a hardcoded filename on the values list on sheet1. This would have to change every month manually in order to prevent the recipients of the workbook overwriting their report every month.

I did create a cell on sheet 1 with =DATE(BudgetStatus!G2,BudgetStatus!G4,1) in it, (where BudgetStatus is sheet 2), which gives me the correct month in mmm format. I tried concatenate in another cell to just get it to read "FEB07", where BudgetStatus!G2 says "2007" but it just gives me the excel date for FEB. So i'm just thinking it would be easier to generate the filename in VBA anyway.

Clear as mud? Thanks in advance!

 

[PHV]

Something like this ?
With Sheets("BudgetStatus")
sName = UCase(Format(DateSerial(.[G2], .[G4], 1), "mmmyy") & Left(sDept, 3)) & nVal2
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat23]

Thanks PHV! I will give this a try first thing in the morning and let you know how it comes out.

 

[jazminecat23]

That rocked! Thanks so much! You have no idea how much time that will save me. Have a star.