Saving multiple selected worksheets from a listbox as separate excel files

Hi all,

I found a code where in when selecting multiple worksheets in a listbox it will be saved as multiple pdf files. What I want to happen is that instead of saving it as pdf, it will be saved as multiple excel files and I want to save it depending on the path I put in Cell J5.

For example, I have a listbox containing Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5.
If I select Sheet1 and Sheet2, the output will be 2 separated excel files containing their data.

The code that I found here: http://www.mrexcel.com/forum/excel-q...stbox-pdf.html is for saving multiple selected worksheets from a listbox as pdf.

Here's the code:

Dim arrSheets()
Dim relativePath As String
Dim idx As Long    ' don't use Selected, that's a listbox property.:)
Dim cnt As Long

    For idx = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(idx) Then
            ReDim Preserve arrSheets(cnt)
            arrSheets(cnt) = ListBox1.List(idx)
            cnt = cnt + 1
        End If
    Next idx

    If cnt > 0 Then
        relativePath = "C:\TEST\" & Sheets("Title").Range("B28").Value
        Sheets(arrSheets).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=relativePath
    End If

Thank you!

Sub tst()
Dim relativePath As String
Dim idx As Long    ' don't use Selected, that's a listbox property.:)

    For idx = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(idx) Then
            relativePath = "C:\TEST\" & ListBox1.Selected(idx) 'change to need
            Sheets(ListBox1.List(idx)).Copy
            With ActiveWorkbook
                .SaveAs relativePath, 51
                .Close True
            End With
        End If
    Next
    
End Sub

Originally Posted by bakerman2

Sub tst()

            relativePath = "C:\TEST\" & ListBox1.Selected(idx) 'change to need

End Sub

Hi bakerman2,

Just wondering, how can I change the relativepath? What if I want to select a folder first then save it?

Thank you for the quick reply! Will test your code.

    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
            If .Show <> -1 Then MsgBox "No folder selected! Exiting sub...": Exit Sub
            mydir = .SelectedItems(1)
    End With
    relativePath = mydir & "\" & ListBox1.Selected(idx) 'change to need