Save as .csv file

[bujaman]

I am trying to write a macro to save each worksheet as a separate .csv file. The file name of the csv file needs to come from cell Z1 of each worksheet, but I am running into some problems, what I think should work, isn't (which is the case in so many aspects of my life!). Here is what I have, any ideas?

[LEFT][COLOR=#000000]Sub SaveAsCSV()[/COLOR][/LEFT]

Dim FName As String    
Dim FPath As String         

FPath = "C:\Users\ben.griggs\Dropbox\BBP\Pricing Sheets\Converted [URL="http://www.xtremevbtalk.com/showthread.php?p=1395029#"][FONT=inherit]Database[/FONT][/URL] Pricing Tables\"    
FName = ActiveSheet.Range("Z1").Text   
   
ActiveWorkbook.SaveAs Filename:=FPath & FName & ".csv", FileFormat:=xlCSVMSDOS

 [LEFT][COLOR=#000000]End Sub[/COLOR][/LEFT]

Thanks!

 

[shg]

Maybe ...

Sub SaveAsCSV()
    Const sPath     As String = "C:\Users\ben.griggs\Dropbox\BBP\Pricing Sheets\Converted Database Pricing Tables\"
    Dim wks         As Worksheet
    Dim sFile       As String

    For Each wks In Worksheets
        wks.Select
        sFile = Range("Z1").Text & ".csv"
        ActiveWorkbook.SaveAs Filename:=sPath & sFile, _
                              FileFormat:=xlCSVMSDOS
    Next wks
End Sub