Need help with a VBA code for distributing data

[cspeid03]

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.

[SamT]

SpecialCells(xlCellTypeVisible) is your friend when copying filtered data.

The Constant, "xlCellTypeVisible," = 12

[cspeid03]

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?

[SamT]

You already know how to filter, you should just have to

FilteredSht.Range.SpecialCells(12).Copy Dest:=NewSheet.Range("A1")