Data Filtering question.

[Guest]

I posted a question yesterday about returning data and received a good answer
about Filtering Data. I need some additional assistance. I have 40+
worksheets in this workbook. Each worksheet contains 8 columns of various
text and numeric data as pertaining to projects. I want to filter the data
on all of the worksheets and return to a separate worksheet all projects that
are behind schedule. Below is an example of the data on the worksheets.
Basically if Column A is NO (Not on target), I want it to return all of the
data in that row to a separate worksheet. I would like all worksheets to
filter this data and return to a single worksheet for a general overview.
Can anyone clarify how I would do this. Thanks in advance!

A B C D E
F G H
On Target Partner Project # Project Mgr. Contact Info Customer Desc.
Date
Yes or NO ABC 1 John Doe Phone # DEF
Robotics 2/4/07

 

[Guest]

Try this VBA code:

It outputs the results to a sheet called "Summary", checking All other
sheets if Column A has value "No".

Sub FilterData()

Dim ws_sumrng As Range
Dim ws As Worksheet
Dim irow As Long
Dim Lastrow As Long

Set ws_sumrng = Worksheets("Summary").Cells(2, "A")
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Activate
With ws
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For irow = 2 To Lastrow
If .Cells(irow, "A") = "No" Then
.Rows(irow).EntireRow.Copy ws_sumrng
Set ws_sumrng = ws_sumrng.Offset(1, 0)
End If
Next irow
End With
End If
Next ws
End Sub

HTH

 

[Guest]

Wow! The formatting was quite different in the post. I manipulated the
layout and hopefully it will look similar to what I am trying to do. 40
Worksheets with 8 Columns (A thru H), a Heading Row and a row of data that
will continuously grow. If there is a NO in Column A, then copy all data in
that row to a "summary" worksheet. All worksheets to report data to
"summary" worksheet.

 

[Guest]

That is exactly what I would like to do! Thank you for the suggestion. The
only problem is that I haven't done anything in the VB editor in years! Any
suggestions? Sorry to be a pain!

 

[Guest]

To insert the code:

Alt+F11 (into Visual Basic Editor VBE)
Alt+I
Select "Module"
copy and paste into "module"

Click Run on toolbar in VBE

HTH

 

[Guest]

Thanks for the instruction. I inserted the code and it appears to do
something (screen "blinks"), but nothing appears on my "Summary" WS.

 

[Guest]

Send w/book to toppers at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

I'll look at in the morning (UK time!)

 

[Guest]

Try to allow for entry of "NO" or "No"


Sub FilterData()


Dim ws_sumrng As Range
Dim ws As Worksheet
Dim irow As Long
Dim Lastrow As Long

Application.ScreenUpdating = False
Set ws_sumrng = Worksheets("Summary").Cells(2, "A")
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Activate
With ws
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For irow = 2 To Lastrow
If StrConv(.Cells(irow, "A"), vbUpperCase) = "NO" Then
.Rows(irow).EntireRow.Copy ws_sumrng
Set ws_sumrng = ws_sumrng.Offset(1, 0)
End If
Next irow
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

HTH

 

[Guest]

You, my new found friend, are awesome. I was away on a day trip yesterday
and checked back today and found your answer. I pasted in your code and it
seems to be working great! I have added new WS to the WB and added data and
it found the new data and added it to it as well as deleting information. It
works wonders! You are awesome. Thank you very much for the help.

 

[Guest]

Thanks for the feedback.

You, my new found friend, are awesome. I was away on a day trip yesterday
and checked back today and found your answer. I pasted in your code and it
seems to be working great! I have added new WS to the WB and added data and
it found the new data and added it to it as well as deleting information. It
works wonders! You are awesome. Thank you very much for the help.