Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel workbook with 55 sheets. I would like to save the sheets as csv files (file name is the name of the excel sheet). When I run the macro, the files are created as desired but the macro messes up the original Excel workbook. The sheets which have formulas, have their cell numbers changed. I want the Excel workbook to retain the formulas for further iterations.

Macro pasted below:

Sub SaveCSV()
Dim ws As Worksheet

Const cstrFOLDER As String = "C:\M-C\HIST"

ChDrive Left(cstrFOLDER, 1)
ChDir cstrFOLDER

Application.DisplayAlerts = False

For Each ws In Worksheets
  ws.Copy
  ActiveWorkbook.SaveAs Filename:=cstrFOLDER & "\" & ws.Name & ".csv", _
        FileFormat:=xlCSV, _
        CreateBackup:=False
  ActiveWorkbook.Close False
Next ws

Application.DisplayAlerts = True

End Sub

Can someone fix the problem? Thanks in advance.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.