PDA

View Full Version : [SOLVED] Export Sheets as CSV and Keep Workbook Open



carpl93
09-05-2018, 01:38 AM
Hi All, newbie here so please forgive ignorance.

I'm looking for a method of exporting every sheet except the first sheet, to a CSV while keeping the main .xlsm file open and sheet 1 active. I have a macro that works fine for saving each sheet as a CSV (Pasted Below) but I can't seem to find any method of essentially running it "in the background" while keeping the original workbook open.



For Each Sheet In ThisWorkbook.Worksheets
If Sheet.Index > 1 Then
SheetNum = Sheet.Index
SheetName = Sheet.Name
WorkbookPath = ThisWorkbook.Path
FilePath = WorkbookPath & "\" & SheetName & ".csv"
Worksheets(SheetNum).Activate
Application.DisplayAlerts = False
Worksheets(SheetNum).SaveAs Filename:=FilePath, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
End If
Next Sheet


Any help greatly appreciated.

Jan Karel Pieterse
09-05-2018, 02:33 AM
The trick is to first copy the sheets to a new workbook. So you would need something like:

Dim oSh As Worksheet
ThisWorkbook.Worksheets.Copy
For Each oSh In ActiveWorkbook.Worksheets
Application.DisplayAlerts = False
oSh.SaveAs Filename:=FilePath, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
Next
ActiveWorkbook.Close False

carpl93
09-06-2018, 12:18 AM
Thanks Jan