Exporting all Sheets on a Spreadsheet to a Single CSV

If you have a spreadsheet with multiple sheets/tabs containing similar tables of data; and you want to export the whole lot to a single CSV, then this VBA macro should help:

Sub ExportAllSheetsToSingleCSV()
'The file to write to
outputFile = "C:\Users\dan\output.csv"
f = FreeFile()
Dim headerLine As String
Open outputFile For Output As f
For Each Sheet In Worksheets
For Each Row In Sheet.Rows
Dim line As String
line = ""
Dim sep As String
sep = ""
Dim lineIsNonEmpty As Boolean
lineIsEmpty = True
'Work through all cells on this row
For Each cell In Row.Cells
If cell <> "" Then
line = line & sep & cell
sep = ","
lineIsEmpty = False
End If
Next
'Did we find anything
If lineIsEmpty = False Then
'Dont write the header line out multiple times
If headerLine <> line Then
Print #f, line
End If
'Set the header line to the first non empty line we find
If headerLine = "" Then
headerLine = line
End If
End If
Next
Next
Close #f
End Sub