Export only filtered data to new workbook
Results 1 to 6 of 6

Thread: Export only filtered data to new workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Export only filtered data to new workbook

    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,773
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Export only filtered data to new workbook

    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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export only filtered data to new workbook

    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?

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,773
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Export only filtered data to new workbook

    Quote Originally Posted by FTBLFAN View Post
    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.SpecialCells(xlCellTypeVisible).Copy l2.Sheets(i + 1).Range("A1")
            l2.Sheets(i + 1).Name = shs(i)
        Next
    
    
        Application.SheetsInNewWorkbook = n
    End Sub
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export only filtered data to new workbook

    I'll check it out.

    Thanks so much for your help!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,773
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Export only filtered data to new workbook

    Quote Originally Posted by FTBLFAN View Post
    I'll check it out.

    Thanks so much for your help!
    Youre welcome, let me know if you have any doubt.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •