Export Each Sheet to CSV

What it does

You have a workbook with a dozen tabs. The downstream system (a Python script, a database loader, a partner who only accepts CSVs) needs each tab as a separate .csv file. Excel's File > Save As > CSV saves only the active sheet, so doing this for 12 tabs means 12 trips through the dialog. The macro saves one CSV per visible sheet in a single pass. Each CSV gets named <workbook>_<sheet>.csv and lands in the same folder as the workbook. Sheet names with characters that are illegal in filenames get sanitized automatically.

Real-world example

Picture a data engineer at a logistics startup who maintains the manual override file for the routing system. The override file is an Excel workbook with one sheet per region, and the routing system ingests CSVs (one per region) every night at 2am. The data engineer's job: every Monday morning he updates the overrides for the new week and re-exports each sheet as a CSV.

His current method: click the first tab, File > Save As, pick CSV, name the file, click Save, click "Yes" to "this format only saves the current sheet", then click the next tab, repeat. About 25 seconds per tab. With 18 regions that's 8 minutes of save-as-clicking, plus the regular Monday treat where he forgets one region, doesn't notice until Wednesday, and the routing system has been using stale data for two days.

With this macro he opens the file, runs the tool, and 18 CSVs appear in the same folder as the workbook in about 2 seconds. The popup confirms how many sheets were exported and where they landed. He never forgets a region because the macro processes every visible sheet automatically.

On an 18-region weekly export, expect about 7 minutes saved per Monday. Across a year of weekly runs that's about 6 hours back, plus the meaningful win of never shipping stale routing data because he forgot a region.

How to use

First time? See How to run our tools for the 30-second setup.

1. Save your workbook to disk first. The macro saves CSVs in the same folder as the workbook, so the workbook needs a real file path.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ExportEachSheetToCSV, click Run.
3. The macro creates one CSV per visible sheet using the naming pattern <workbook>_<sheet>.csv and reports the count and folder.

FAQ

Where do the CSVs get saved?

In the same folder as the workbook itself. So if your workbook is at /Users/me/Documents/Routing.xlsm, the CSVs land in /Users/me/Documents/. Sheet names get sanitized (slashes, colons, asterisks become underscores) so the filenames are always legal. If a CSV with the same name already exists in the folder, it gets overwritten without warning.

Does it export hidden sheets?

No. The macro skips sheets where Visible <> xlSheetVisible for the same reason as the autofit and freeze macros: hidden sheets usually aren't intended to ship downstream. If you need a hidden sheet exported, unhide it temporarily, run the macro, then re-hide.

What about formulas, formatting, and merged cells?

CSV is a flat plain-text format that doesn't carry formulas, formatting, or merged cells. So formulas export as their evaluated values, formatting is dropped, and merged cells become single-value cells with the merge's value in the top-left and blanks elsewhere (Excel's default CSV behavior). For multi-sheet workbooks where this matters, freeze formulas first with Convert Formulas to Values so the CSV captures the values you expect.

Does it work on Mac Excel?

Yes, with one caveat: macOS sandbox security may prompt you to grant access to the workbook's folder the first time you run the macro. Click "Allow" when prompted, and after that the macro runs without further dialogs. The macro uses Application.PathSeparator so the path joins correctly on both Windows and Mac.

Can I undo it?

CSV creation is a file-system action, not an in-document edit, so Ctrl/Cmd + Z does NOT delete the CSVs. To remove them, delete them from the folder via Finder or Explorer. The macro runs entirely offline. Your data never leaves your machine; the CSVs are written to your local disk only.