Export only filtered data to new workbook

FTBLFAN

New Member
Joined
Jul 24, 2019
Messages
3
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Test if this works for you, copy everything except formulas.


Code:
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
 
Upvote 0
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?
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top