Export to CSV rows populated

You can apply an Autofilter to row5, then filter the data by column A for all rows that "<>" (not blank), that would hide all the blank rows. Then copy all visible rows to a new sheet, then save that sheet as a CSV. All pretty standard.

1) What is the path to save the CSV?
2) What is the name of the CSV each time it is saved? Is it the same each time, or should it increment in some manner?
3) Will column F always have data in every row? It's important the answer is "yes" if a simple autofilter approach is to work correctly.


It would be much simpler to help directly if you post your workbook. Click GO ADVANCED and use the paperclip icon to attach your sample workbook.

You can put this macro into a standard code module (Insert > Module).
Then connect all your buttons on each of the sheets to the same macro, it will work for each and name each CSV for the same name as the sheet from which it is triggered.

Option Explicit

Sub ExportToCSV()
Dim fPATH As String, fNAME As String, LR As Long

fNAME = ActiveSheet.Name
fPATH = ThisWorkbook.Path & Application.PathSeparator
Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    .Rows("5:5").AutoFilter Field:=1, Criteria1:="<>"
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR > 5 Then
        .Range("A5:F" & LR).Copy
        Sheets.Add
        Range("A1").PasteSpecial xlPasteAll
        ActiveSheet.Move
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs fPATH & fNAME & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close False
        MsgBox "Export file done: " & fPATH & fNAME & ".csv"
    Else
        MsgBox "No rows with data found to export"
    End If
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True
End Sub

1) Change this:

Range("A1").PasteSpecial xlPasteValues

2) I thought I saw a button on the pages where you wanted this to run. Won't you just be clicking your button on any given page to run it?

Option Explicit

Sub ExportToCSV()
Dim fPATH As String, LR As Long
Dim ws As Worksheet

fPATH = ThisWorkbook.Path & Application.PathSeparator
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    With ws
        If ws.Name Like "Scenario*" Then
            .AutoFilterMode = False
            .Rows("5:5").AutoFilter Field:=1, Criteria1:="<>"
            LR = .Range("A" & .Rows.Count).End(xlUp).Row
            If LR > 5 Then
                .Range("A5:F" & LR).Copy
                Sheets.Add
                Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
                ActiveSheet.Move
                Application.DisplayAlerts = False
                ActiveWorkbook.SaveAs fPATH & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
                ActiveWorkbook.Close False
                MsgBox "Export file done: " & fPATH & ws.Name & ".csv"
            Else
                MsgBox "No rows with data found to export"
            End If
            .AutoFilterMode = False
        End If
    End With
Next ws

Application.ScreenUpdating = True
End Sub

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

My bad, I meant to move the naming variable within the FOR/NEXT loop and just forgot. Corrected above, grab that code again.

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.