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

--
Mr. Brown

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


"KUKA Guy" wrote:

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

--
Mr. Brown

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!
--
Mr. Brown


"Toppers" wrote:

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


"KUKA Guy" wrote:

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

--
Mr. Brown

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


"KUKA Guy" wrote:

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!
--
Mr. Brown


"Toppers" wrote:

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


"KUKA Guy" wrote:

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

--
Mr. Brown

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


"Toppers" wrote:

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


"KUKA Guy" wrote:

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!
--
Mr. Brown


"Toppers" wrote:

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


"KUKA Guy" wrote:

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

--
Mr. Brown

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

remove NOSPAM

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

"KUKA Guy" wrote:

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


"Toppers" wrote:

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


"KUKA Guy" wrote:

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!
--
Mr. Brown


"Toppers" wrote:

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


"KUKA Guy" wrote:

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

--
Mr. Brown

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.
--
Mr. Brown


"KUKA Guy" wrote:

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
Target Partner Project # Project Mgr. Contact Info Customer Desc. Date
Yes or NO ABC 1 John Doe Phone # DEF Robotics 2/4/07

--
Mr. Brown