pastespecial method of worksheet class failed

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.

THanks for the quick reply..,.
so does that mean I can define name range in the ActiveSheet.Range("NAMED RANGE").PasteSpecial xlPasteValues ?

ACtually I got - application object error object defined error for this step :

 ActiveSheet.Range("Day_AC_Out").PasteSpecial xlPasteValues

ACtually, it works if put in the actual range and not mention the DEFINE NAME, as follows :-

ThisWorkbook.Names("All_Data_v2").RefersToRange.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Range("$B$39:$O$74").PasteSpecial xlPasteValues
    Application.CutCopyMode = False

I can live with the above approach by defining the range values and not names,

However whenever I try to hit SAVE button > it runs the macro successfully but also crashes the EXCEL (giving me option to restart Excel)...

whats causing this ?

it is All_Data_V2...

And also after implementing the changes you mentioned

ThisWorkbook.Names("All_Data_v2").RefersToRange.Cells(1).Copy

It is now not copying the data in the whole range but jst the first cell.

will I need to define the range instead of using .Cells(1) place ?

ok.. one more update > if I comment out the line

 End With
    ActiveWorkbook.Save
    'ActiveWindow.Close
    Range("All_Data_v2").Select
    'Selection.ClearContents

This stops the EXCEL to CRASH and the process works just fine.

Thanks mate.

Can you tell me the difference in using complete Range in the syntax v/s using Range.Cells(1) approach ?

What does or will this code "range.cells(1)" do ?

That makes sense... but in my case I am defining a range from it needs to copy the values and paste it in a new file so in effective it is pasting from A1 location onwards.
Hence it wont apply to this situation right ?

Also can I ask you, If I want to run the same code 9 times for each range then I will copy the code one below the other 9 times and jst change the filename / Define Range / Actual Range for each one of them.
And when I hit the save button it will create 9 different files for each range defined.

I tried doing this, initially for 2 more range but it came with an error of Ambiguous name detected SUB sheetToCsv()

What do I need to do over here ?

one uses loop to rerun the same code again and again..till a condition is met,

but over here I do need to run the same code again but I have to change the file name for each range and a different range_name and Range of data for 9 different Ranges.
How would 1 do this activity ?

Sorry mate,

Can you show me for jst 2 such ranges..in a loop from my codes above, please.

Cross post http://www.ozgrid.com/forum/showthread.php?t=187649

apologies Stephen... i thought I am using 2 different forumns 1. OZGRID forum 2. excelforum and hence my double post.

did'nt knew they basically point to the same underlying forum.