Filter specific data to a new workbook

[simora]

I have a workbook with 12 sheets, DataBook.xls And another blank workbook open.
( New Data.xls )

I want to Filter column D in the DataBook for “HD” on all the 12 worksheets and create a new worksheet in the New Data workbook to match each of the old worksheets, then copy all rows that match to the new worksheet in the New Data workbook.

So Sheet 1 gets copied to sheet 1 etc... in the new workbook.

The copied worksheet must retain its original name and formatting if possible in the new workbook.

Any help appreciated.
Using Excel 2003 & Win XP

[mancubus]

hi. try this.
copy the code to orginal workbook's code module.


Sub new_wb_with_filtered_data()
Worksheets.Copy
With ActiveWorkbook
For Each ws In .Worksheets
With ws
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=4, Criteria1:="<>HD"
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilterMode = False
End With
Next
.SaveAs ThisWorkbook.Path & "\" & "New Data", FileFormat:=56
End With
End Sub

[simora]

Thanks, but the code gives an error here.
.Cells(1).AutoFilter Field:=4, Criteria1:="<>HD"

This is the error

AutoFilter method of Range Class Failed.

The code is just copying the whole workbook. Its NOT filtering.

[mancubus]

welcome.

perhaps your workbook contains a blank worksheet or a worksheet different from 12 worksheets.

either delete unrelated worksheets or modify the first line to include desired worksheets only. such as:



Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

[mdmackillop]

Try this line

.Columns(4).AutoFilter Field:=1, Criteria1:="<>HD"

[mancubus]

mdmackillop's suggestion resolves, for example, tables do not start at cell A1, there are blank columns in the table, etc. What if the worksheet (or column D) is blank.

Organizing the tables before running a macro is of critical importance.

PS: i assume ranges are contiguous and their top left cells are A1 unless otherwise stated by the OP.

[simora]

Thanks ALL!
mdmackillop's suggestion of using .Columns(4).AutoFilter Field:=1, Criteria1:="<>HD"
worked great. Excel 2003 did NOT like the
FileFormat:=56

Section of the code. Deleting it and simply naming the file .xls worked.