Consulting

Results 1 to 4 of 4

Thread: Need help with a VBA code for distributing data

  1. #1
    VBAX Newbie
    Joined
    Dec 2016
    Posts
    2
    Location

    Need help with a VBA code for distributing data

    Hello,



    I got a work project that requires me to create several documents out one Master Document.

    Here's the problem, the master document has filter cell, which I need in order to filter the massive amount of data and then create a new document with only the filtered data. The problem is that I have to create a large amount of those new documents, and copy-pasting will take forever. I thought I had solved the problem by copying the master sheet several times and then filtering the information on each sheet (each sheet has different filters) and then using the code below to make every sheet into a new document. This seem like a viable solution until I realized it copies the complete data but only showed the filtered data.




    Sub Splitbook()
    Dim xPath As String
    xPath = Application.ActiveWorkbook.Path
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xls"
    Application.ActiveWorkbook.Close False
    Next
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

    What I need is a code that will copy the filtered data WITHOUT the master data and create a new doc with only the filtered data.

    Example. (Bold means that the data can be filtered)


    A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 ( MASTER DATA)

    A1 A3 A4 B1 B3 B4 C1 C3 C4 (FILTERED DATA - AL CELLS CONTAINING "2" ARE NOT SHOWN)

    A1 A3 A4 B1 B3 B4 C1 C3 C4 (NEW DOWCUMENT)

    The new document will only contain the filtered data. Not the master data filtered.



    Thanks in advance.
    Last edited by cspeid03; 12-21-2016 at 02:04 PM.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,048
    Location
    SpecialCells(xlCellTypeVisible) is your friend when copying filtered data.

    The Constant, "xlCellTypeVisible," = 12
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Dec 2016
    Posts
    2
    Location
    I've been trying to use SpecialCells but cant make it work. Im not that keen with VBA.
    Can you send me the final code?

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,048
    Location
    You already know how to filter, you should just have to
    FilteredSht.Range.SpecialCells(12).Copy Dest:=NewSheet.Range("A1")
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

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