cdeleon
11-02-2012, 06:47 PM
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
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