Hi
I am actually trying to copy named ranges (have 10 different named range sections) to a new csv file for each of the named range.

Instead of copying the formula forma, I want to copy just the values of the named range.

So here is my macro in MODULE :-



VB:
Sub sheetToCSV() 
    Dim MyPath As String 
    Dim MyFileName As String 
     'The path and file names:
    MyPath = "D:\_ShopFloor BI queries" 
    MyFileName = "MyFileName" & Format(Date, "ddmmyy") 
     'Validation: Making sure the path name ends with "\":
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\" 
     'validation: Making sure the filename ends with ".csv"
    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv" 
     'This copies the sheet to a new workbook. Change the name between "" to your sheet name.
     'Sheets("Book2").Copy
    ThisWorkbook.Names("Day_AC_Out").RefersToRange.Select 
    Selection.Copy 
    Workbooks.Add 
    ActiveSheet.PasteSpecial xlPasteValues 
    Application.CutCopyMode = False 
     'The new workbook becomes Activeworkbook:
    With ActiveWorkbook 
         'Saves the new workbook to given folder / filename:
        .SaveAs Filename:= _ 
        MyPath & MyFileName, _ 
        FileFormat:=xlCSV, _ 
        CreateBackup:=False 
         'Closes the file
        .Close False 
    End With 
    ActiveWorkbook.Save 
    ActiveWindow.Close 
    Range("Day_AC_Out").Select 
    Selection.ClearContents 
End Sub
And MACRO IN THISWORKBOOK:-



VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Call sheetToCSV 
End Sub
I am getting ERROR in the module section " ActiveSheet.PasteSpecial xlPasteValues"
Any suggestions...

ps:- I am pretty new to VB macro stuff so plz be patient.