I have a workbook comprised of several worksheets. I need to print any row
with a word "high" in any worksheet to a new worksheet of all records
containing word "high" only. Please help!

How about something like this:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

Set ActiveSheet = C_Sheet

Sheets.Add
Activesheet.Name = "Summary"

C_Sheet.Select

For Each cell In ActiveSheet.UsedRange
If cell Like "*high*" Or Then
cell.EntireRow.Copy Destination:=Sheets("Summary").Range("A1").Offset( i, 0)
i = i + 1
End If
Next cell

End Sub

Hope this helps!
--
-SA


"Loc" wrote:

I have a workbook comprised of several worksheets. I need to print any row
with a word "high" in any worksheet to a new worksheet of all records
containing word "high" only. Please help!

Hello SA,

Thank you for your help. It seems like a programming not just a simple
formula as I expected. I have 2 questions for you:

1) Where do I write this program for the workbook I have?
2) How do I program the workbook to search entire book not just sheet and
copy the entire records containing word "high" to the "Summary" worksheet?

If you don't mind, please show me step-by-step how to make this process work

Loc

"StumpedAgain" wrote:

How about something like this:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

Set ActiveSheet = C_Sheet

Sheets.Add
Activesheet.Name = "Summary"

C_Sheet.Select

For Each cell In ActiveSheet.UsedRange
If cell Like "*high*" Or Then
cell.EntireRow.Copy Destination:=Sheets("Summary").Range("A1").Offset( i, 0)
i = i + 1
End If
Next cell

End Sub

Hope this helps!
--
-SA


"Loc" wrote:

I have a workbook comprised of several worksheets. I need to print any row
with a word "high" in any worksheet to a new worksheet of all records
containing word "high" only. Please help!

Is the word "high" found in a specific column of every sheet or can it be in
any column in every sheet? Can the word be found more than once in any one
row? Are there any sheets that you DON'T want searched, like for instance,
the Summary sheet? What columns comprise the "entire record"? Where in the
Summary sheet do you want the data placed? HTH Otto
"Loc" wrote in message
...
Hello SA,

Thank you for your help. It seems like a programming not just a simple
formula as I expected. I have 2 questions for you:

1) Where do I write this program for the workbook I have?
2) How do I program the workbook to search entire book not just sheet and
copy the entire records containing word "high" to the "Summary" worksheet?

If you don't mind, please show me step-by-step how to make this process
work

Loc

"StumpedAgain" wrote:

How about something like this:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

Set ActiveSheet = C_Sheet

Sheets.Add
Activesheet.Name = "Summary"

C_Sheet.Select

For Each cell In ActiveSheet.UsedRange
If cell Like "*high*" Or Then
cell.EntireRow.Copy Destination:=Sheets("Summary").Range("A1").Offset( i,
0)
i = i + 1
End If
Next cell

End Sub

Hope this helps!
--
-SA


"Loc" wrote:

I have a workbook comprised of several worksheets. I need to print any
row
with a word "high" in any worksheet to a new worksheet of all records
containing word "high" only. Please help!

Otto,

The word "high" is in column K only in every sheet and appears only once in
any row. I would like it searches for the word "high" in entire workbook
except the Summary sheet. Each record has 13 columns from A - M. If any
record has the word "high" in column K, then the entire record will be copy
to the Summary sheet in the same workbook. The Summary sheet has 13 columns
like other worksheet.

I would appreciate if you can show me how (step-by-step) to program this
complicated process. Thank you.

"Otto Moehrbach" wrote:

Is the word "high" found in a specific column of every sheet or can it be in
any column in every sheet? Can the word be found more than once in any one
row? Are there any sheets that you DON'T want searched, like for instance,
the Summary sheet? What columns comprise the "entire record"? Where in the
Summary sheet do you want the data placed? HTH Otto
"Loc" wrote in message
...
Hello SA,

Thank you for your help. It seems like a programming not just a simple
formula as I expected. I have 2 questions for you:

1) Where do I write this program for the workbook I have?
2) How do I program the workbook to search entire book not just sheet and
copy the entire records containing word "high" to the "Summary" worksheet?

If you don't mind, please show me step-by-step how to make this process
work

Loc

"StumpedAgain" wrote:

How about something like this:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

Set ActiveSheet = C_Sheet

Sheets.Add
Activesheet.Name = "Summary"

C_Sheet.Select

For Each cell In ActiveSheet.UsedRange
If cell Like "*high*" Or Then
cell.EntireRow.Copy Destination:=Sheets("Summary").Range("A1").Offset( i,
0)
i = i + 1
End If
Next cell

End Sub

Hope this helps!
--
-SA


"Loc" wrote:

I have a workbook comprised of several worksheets. I need to print any
row
with a word "high" in any worksheet to a new worksheet of all records
containing word "high" only. Please help!

Loc
Here is a macro that will do what you want. I assumed that all your
headers are in row 1 and your data starts in row 2. I realize that this is
all new to you and I'm afraid that if I explain it all it would only confuse
you more. If you wish, send me an email and I'll send you the small file I
used to develop this, with the macro properly placed and a button in the
Summary sheet to activate the macro. Or you can send me your file or a file
with dummy data and I'll place the macro and button and send it back. My
email address is . Remove the "extra" from
this address. HTH Otto
Sub CopyHigh()
Dim Dest As Range
Dim i As Range
Dim rColK As Range
Dim ws As Worksheet
Sheets("Summary").Select
Set Dest = Range("A2")
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Summary" Then
With ws
Set rColK = .Range("K2", .Range("K" &
Rows.Count).End(xlUp))
For Each i In rColK
If UCase(i.Value) = "HIGH" Then
i.Offset(, -10).Resize(, 13).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub
"Loc" wrote in message
...
Otto,

The word "high" is in column K only in every sheet and appears only once
in
any row. I would like it searches for the word "high" in entire workbook
except the Summary sheet. Each record has 13 columns from A - M. If any
record has the word "high" in column K, then the entire record will be
copy
to the Summary sheet in the same workbook. The Summary sheet has 13
columns
like other worksheet.

I would appreciate if you can show me how (step-by-step) to program this
complicated process. Thank you.

"Otto Moehrbach" wrote:

Is the word "high" found in a specific column of every sheet or can it be
in
any column in every sheet? Can the word be found more than once in any
one
row? Are there any sheets that you DON'T want searched, like for
instance,
the Summary sheet? What columns comprise the "entire record"? Where in
the
Summary sheet do you want the data placed? HTH Otto
"Loc" wrote in message
...
Hello SA,

Thank you for your help. It seems like a programming not just a simple
formula as I expected. I have 2 questions for you:

1) Where do I write this program for the workbook I have?
2) How do I program the workbook to search entire book not just sheet
and
copy the entire records containing word "high" to the "Summary"
worksheet?

If you don't mind, please show me step-by-step how to make this process
work

Loc

"StumpedAgain" wrote:

How about something like this:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

Set ActiveSheet = C_Sheet

Sheets.Add
Activesheet.Name = "Summary"

C_Sheet.Select

For Each cell In ActiveSheet.UsedRange
If cell Like "*high*" Or Then
cell.EntireRow.Copy
Destination:=Sheets("Summary").Range("A1").Offset( i,
0)
i = i + 1
End If
Next cell

End Sub

Hope this helps!
--
-SA


"Loc" wrote:

I have a workbook comprised of several worksheets. I need to print
any
row
with a word "high" in any worksheet to a new worksheet of all
records
containing word "high" only. Please help!