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:

Please Login or Register  to view this content.

And MACRO IN THISWORKBOOK:-



VB:

Please Login or Register  to view this content.

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 :

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.