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.

deleted......

Hi cspeid03,

I think you are looking for the Advanced*Filter, where you filter the master data to another sheet (or a different part of the same sheet) and then copy that data.
Read about them at:
http://www.excel-easy.com/examples/advanced-filter.html