Macro to "save as"

[bluepenink]

Hello

Can someone pls help me with a macro to create new workbooks?

i want to save a workbook from cell N3 on worksheet called "Branch"

i want to path to be saved on desktop in a folder called "Bonus"

-the file name needs to be in the following way:

i.e.
Employee name from N3 THEN (vlookup on SE list for district) THEN V.1.0 THEN (today's date)

for example > save as >excel 03 > file name
John Doe (New York-Long Island) V.1.0 (Sep 6, 2011)

I have the district the person belongs to in the worksheet "SE List", so if you can pls help me with this macro that would be great, as there are over 100 employees i have to do this for in my main sheet. also, if you can define the name so if i expand teh selection, it would not throw off the macro.

pls and thx u so much!

Option Explicit
 
Sub SaveAsSpecial()
 
With ActiveWorkbook.Sheets("Bonus")
If .[N3] <> "" Then
ActiveWorkbook.SaveAs .[N3] & " (" & _
Application.VLookup(N3, Sheets("SE List").Range("C:E"), 3, 0) _
& ") V.1.0 (" & Format(Date, "MMM D, YYYY") & ").xls", xlNormal
End If
End With
 
End Sub

the above macro is not working and nor is saving a file in a folder...can u pls help

 

[bluepenink]

my error comes on the line [N3]

not sure why it maybe so....can someone help!

 

[bluepenink]

any suggestions guys?

 

[AlphaFrog]

Sub SaveAsSpecial()
    
    Dim Employee As String, rngDistrict As Range
    
    Employee = ActiveWorkbook.Sheets("Bonus").Range("N3").Value
    
    If Employee = "" Then
        MsgBox "'Bonus'!N3 is empty.", vbExclamation, "No Employee"
    Else
        Set rngDistrict = Sheets("SE List").Range("C:C").Find(What:=Employee, _
                                                              LookIn:=xlValues, _
                                                              LookAt:=xlWhole, _
                                                              MatchCase:=False)
        If rngDistrict Is Nothing Then
            MsgBox "Cannot match """ & Employee & """ on sheet ""SE List""", _
                    vbExclamation, "No Employee Match"
        Else
            ActiveWorkbook.SaveAs Employee & " (" & rngDistrict.Offset(, 2).Value & _
                                  ") V.1.0 (" & Format(Date, "MMM D, YYYY") & ").xls", xlNormal
            MsgBox ActiveWorkbook.FullName, _
                    vbInformation, "Save Complete"
        End If
    End If
    
End Sub

 

[Bill C]

You have to declare your variables. I would record a macro and fill in all the stuff that is the same on the sheet and then edit the macro for the save as part of the code. Test each part of the code so you know what works. That would help a lot. I am new to this myself too but just keep at it. There is no better feeling than to say, "I did that".
Bill C

 

[bluepenink]

Sub SaveAsSpecial()
 
    Dim Employee As String, rngDistrict As Range
 
    Employee = ActiveWorkbook.Sheets("Bonus").Range("N3").Value
 
    If Employee = "" Then
        MsgBox "'Bonus'!N3 is empty.", vbExclamation, "No Employee"
    Else
        Set rngDistrict = Sheets("SE List").Range("C:C").Find(What:=Employee, _
                                                              LookIn:=xlValues, _
                                                              LookAt:=xlWhole, _
                                                              MatchCase:=False)
        If rngDistrict Is Nothing Then
            MsgBox "Cannot match """ & Employee & """ on sheet ""SE List""", _
                    vbExclamation, "No Employee Match"
        Else
            ActiveWorkbook.SaveAs Employee & " (" & rngDistrict.Offset(, 2).Value & _
                                  ") V.1.0 (" & Format(Date, "MMM D, YYYY") & ").xls", xlNormal
            MsgBox ActiveWorkbook.FullName, _
                    vbInformation, "Save Complete"
        End If
    End If
 
End Sub

hello thx u so much ..makes sense, i appreciate the prompt response.

quick question, how can i change the path to where the file is saved?
thxs again bud!

 

[AlphaFrog]

Put something like this before you save as.

ChDir "C:\MyFolder\MySubfolder\"

This doesn't change drive letters. Use ChDrive to do that.


Or include the path in the save as

strMyPath = "C:\MyFolder\MySubfolder\"
ActiveWorkbook.SaveAs [COLOR="Red"]strMyPath &[/COLOR] Employee & " (" & rngDistrict.Offset(, 2).Value & _
                                  ") V.1.0 (" & Format(Date, "MMM D, YYYY") & ").xls", xlNormal

 

[bluepenink]

thxs alot pal!

great help!!!!

 

[Bill C]

Alphafrog,
I am impressed at what you have done in this code and what incredible things excel can do. Good work!

Bill C