Export Multiple Sheets to .csv

[ForrestGump01]

Hello all,

My workbook has an instructions/input tab, a few hidden lookup tabs, and a bunch of hidden output tabs (currently 10 of them). I would like to export/save as separate files the output tabs based on whether there is data <>"" in the tab or not (excluding the header row 1), and save them as .csv files with file names that reference an input cell on the input tab and the exporting tab name... I would also like to create a dialog box where the user can select what folder these files save into.

Each of the 10 output tabs might have data between rows 1-100, but not all 10 tabs will always have data in them... Example: currently output tabs "Pg 1", "Pg 2", and "Pg 3" have data in them. I would like these three tabs to export as .csv's when the user clicks the macro button on the instructions/input tab as a file name from cell D17 on the input page + the name of the exporting tab ("Pg 1"... etc).

It would also be great if at the end of the export the vba saved the macro file and closed the workbook.


In the end, I would like the file to export any of the output tabs that have data excluding the header row that is not equal to "" as .csv files with specific names, in a user-selected folder, and save the whole workbook as a macro-enabled file in the same folder, then close the workbook. Any help is greatly appreciated.

 

[sijpie]

THis should do the trick. Read the comments, particularly the one starting with <<<<

Option Explicit

Sub ExportPg()
'Export all tabs with names "Pg x" as PDF _
 Only those tabs that contain data
 
    Dim wsWS As Worksheet
    Dim bHidden As XlSheetVisibility
    Dim sPrefix As String
    
    sPrefix = ActiveSheet.Range("D17")      '<<<<< The macro can only be run from the sheet with the name prefix in D17
                                            ' If you want to run the macro from another tab, then change 'Activesheet' to 'Sheets("sheet name")' _
                                              where 'Sheet name' is the name of the sheet holding the prefix in D17
    sPrefix = sPrefix & " - "
    For Each wsWS In ThisWorkbook.Worksheets
        If LCase(wsWS.Name) Like "pg*" Then ' Only sheets starting with "Pg" are processed for export to pdf
            bHidden = wsWS.Visible          ' store the visible status of the sheet
            If wsWS.Range("A1").CurrentRegion.Rows.Count > 1 Then
                'contains more than just the header row
                'so print as pdf
                wsWS.Visible = xlSheetVisible
                wsWS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPrefix & wsWS.Name & ".pdf", Quality:=xlQualityStandard, _
                        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
            End If
            wsWS.Visible = xlSheetHidden    ' restore the visible status of the sheet
        End If
    Next wsWS
 
End Sub