Export only filtered data to new workbook

[FTBLFAN]

I have a workbook which contains data for all of our vendors. It is set up to filter every sheet based on a dropdown on the first sheet. I would like a ‘button’ to create a new workbook containing the formulas, buttons, formatting, etc. from the original - but only with the selected data.

I hope to give the workbook to each individual vendor, but don’t want them to have access to other vendors’ data.

I have a very simple macro that exports the workbook to a new copy, but all of the underlying data also goes.

Here’s what I have:
Sub ()

ThisWorkbook.Sheets(Array(“Summary”, “Suggested”, “Selected”)).copy

End Sub()

Thanks in advance for any help.

 

[DanteAmor]

Test if this works for you, copy everything except formulas.

Sub Export_only_filtered_data()
    Dim sh As Worksheet, n As Long, l1 As Workbook, l2 As Workbook, shs As Variant, i As Long
    Application.ScreenUpdating = False
    
    shs = Array("Summary", "Suggested", "Selected")
    
    n = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = UBound(shs) + 1
    Set l1 = ThisWorkbook
    Set l2 = Workbooks.Add
    For i = 0 To UBound(shs)
        l1.Sheets(shs(i)).Cells.Copy l2.Sheets(i + 1).Range("A1")
        l2.Sheets(i + 1).Name = shs(i)
    Next


    Application.SheetsInNewWorkbook = n
End Sub

 

[FTBLFAN]

Thanks so much, but this is still bringing in all of the data.
I believe I don't understand how the Range function works.

Where you have Range("A1"). Should that be pointing to my hidden sheet which has the list of values used in the filter, or should it be the cell which is being used as the dropdown?

 

[DanteAmor]

Thanks so much, but this is still bringing in all of the data.
I believe I don't understand how the Range function works.

Where you have Range("A1"). Should that be pointing to my hidden sheet which has the list of values used in the filter, or should it be the cell which is being used as the dropdown?

Works for me. I guess your sheets are already filtered, so try the following:

Sub Export_only_filtered_data()
    Dim sh As Worksheet, n As Long, l1 As Workbook, l2 As Workbook, shs As Variant, i As Long
    Application.ScreenUpdating = False
    
    shs = Array("Summary", "Suggested", "Selected")
    
    n = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = UBound(shs) + 1
    Set l1 = ThisWorkbook
    Set l2 = Workbooks.Add
    For i = 0 To UBound(shs)
        l1.Sheets(shs(i)).Cells.[COLOR=#0000ff]SpecialCells(xlCellTypeVisible)[/COLOR].Copy l2.Sheets(i + 1).Range("A1")
        l2.Sheets(i + 1).Name = shs(i)
    Next


    Application.SheetsInNewWorkbook = n
End Sub

 

[FTBLFAN]

I'll check it out.

Thanks so much for your help!

 

[DanteAmor]

I'll check it out.

Thanks so much for your help!

Youre welcome, let me know if you have any doubt.