Error 1004 copy of object failed

[cdeleon]

I need help. I have a macro in my excel workbook that takes each worksheet and saves it to individual csv file however, I have upgraded to Office 2010 and my code does not work. I get a runtime error 1004 copy of object. Did something change? Any help would be appreciated. The debug highlights wks.Copy 'to a new workbook as the problem. Below is the code.

Sub CSVGenerator()
Dim newWks As Worksheet
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="D:\CSVs\" & wks.Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks
MsgBox "FINISHED GENERATING CSV: " & ActiveWorkbook.Name
End Sub

[p45cal]

It works here on 2010 if no sheet is hidden…

[cdeleon]

Thanks for checking it out. I don't have any sheets hidden so maybe it is a permissions problem to the directory I am writing it to. At least I know that it is not a 2010 problem.

[p45cal]

I doubt it, that line doesn't try to write anything to a folder.
Are you sure there are no veryHidden sheets (you won't see them when you try to unhide them in the normal Excel GUI - but you will see them in the Project Explorer pane of the VBE).

[shrivallabha]

I doubt it, that line doesn't try to write anything to a folder.
Are you sure there are no veryHidden sheets (you won't see them when you try to unhide them in the normal Excel GUI - but you will see them in the Project Explorer pane of the VBE).
A very hidden sheet can be the cause. cdeleon, refer the link:
http://www.mrexcel.com/forum/excel-questions/665227-1004-select-method-worksheet-class-failed-2.html