Copy rows from all sheets based on cell value

Hi all. I have a workbook that has 100+ sheets in it. I added a sheet
(sheet1) , and in cell A1 of that sheet is the word "Payment".

I would like to cycle through every sheet, and copy every row that has the
word "payment" in column B into Sheet1, one after the other.

Anyone know how I can do this? Also, I would love to somehow tag each line
with the Sheetname it came from. Possible? Thanks!

Hello Steph,

Here is a macro that searches Column "B" of each worksheet and pastes the Worksheet name and the data onto "Sheet1" under Column "B" if the word payment is found. If you need any help, just e-mail me LeithRossAaol.com
_________________________________________________________________

Public Sub GetPayments()

Dim N As Long
Dim R As Long
Dim W As Long

Dim Wks As Worksheet
Dim MainWks As Worksheet

Set MainWks = Worksheets("Sheet1")

'Loop through the Worksheets
For W = 2 To Worksheets.Count
Set Wks = Worksheets(W)

'Loop Through each Row in Coulmn "B"
For R = 1 To Wks.WorksheetFunction.CountA(Range("B:B"))

'Examine Column "B" for "Payment"
If Wks.Cells(R, 2).Value = "Payment" Then

'N = Entries made on "Sheet1" coulmn "B"
N = N + 1

'Copy Worksheet Name to Data to "Sheet1" Column "B"
MainWks.Cells(N, 2).Value = Wks.Name & " - " & Wks.Cells(R,2).Value

End If

'Get Next Row
Next R

'Get Next Worksheet
Next W

End Sub

_________________________________________________________________

Hope this helps,
Leith Ross

Give this code a try...

Option Explicit

Private Sub CopyPayments()
Dim rngFound As Range
Dim rngStart As Range
Dim rngPaste As Range
Dim wks As Worksheet

Set rngPaste = Sheet1.Range("A2")

For Each wks In Worksheets
If wks.Name <> Sheet1.Name Then
Set rngFound = wks.Cells.Find("payment", , , xlWhole)
Set rngStart = rngFound

Do
rngFound.EntireRow.Copy rngPaste
Set rngFound = wks.Cells.FindNext(rngFound)
Set rngPaste = rngPaste.Offset(1, 0)
Loop Until rngStart.Address = rngFound.Address
Set rngStart = Nothing
End If
Next wks
End Sub

HTH

"Steph" wrote:

> Hi all. I have a workbook that has 100+ sheets in it. I added a sheet
> (sheet1) , and in cell A1 of that sheet is the word "Payment".
>
> I would like to cycle through every sheet, and copy every row that has the
> word "payment" in column B into Sheet1, one after the other.
>
> Anyone know how I can do this? Also, I would love to somehow tag each line
> with the Sheetname it came from. Possible? Thanks!
>
>
>
>
>

The previous code assumes that there will be a payment on each sheet. If that
is not the case then try this...

Option Explicit

Private Sub CopyPayments()
Dim rngFound As Range
Dim rngStart As Range
Dim rngPaste As Range
Dim wks As Worksheet

Set rngPaste = Sheet1.Range("A2")

For Each wks In Worksheets
If wks.Name <> Sheet1.Name Then
Set rngFound = wks.Cells.Find("payment", , , xlWhole)
Set rngStart = rngFound
If Not rngFound Is Nothing Then
Do
rngFound.EntireRow.Copy rngPaste
Set rngFound = wks.Cells.FindNext(rngFound)
Set rngPaste = rngPaste.Offset(1, 0)
Loop Until rngStart.Address = rngFound.Address
Set rngStart = Nothing
End If
End If
Next wks
End Sub

HTH

"Jim Thomlinson" wrote:

> Give this code a try...
>
> Option Explicit
>
> Private Sub CopyPayments()
> Dim rngFound As Range
> Dim rngStart As Range
> Dim rngPaste As Range
> Dim wks As Worksheet
>
> Set rngPaste = Sheet1.Range("A2")
>
> For Each wks In Worksheets
> If wks.Name <> Sheet1.Name Then
> Set rngFound = wks.Cells.Find("payment", , , xlWhole)
> Set rngStart = rngFound
>
> Do
> rngFound.EntireRow.Copy rngPaste
> Set rngFound = wks.Cells.FindNext(rngFound)
> Set rngPaste = rngPaste.Offset(1, 0)
> Loop Until rngStart.Address = rngFound.Address
> Set rngStart = Nothing
> End If
> Next wks
> End Sub
>
> HTH
>
> "Steph" wrote:
>
> > Hi all. I have a workbook that has 100+ sheets in it. I added a sheet
> > (sheet1) , and in cell A1 of that sheet is the word "Payment".
> >
> > I would like to cycle through every sheet, and copy every row that has the
> > word "payment" in column B into Sheet1, one after the other.
> >
> > Anyone know how I can do this? Also, I would love to somehow tag each line
> > with the Sheetname it came from. Possible? Thanks!
> >
> >
> >
> >
> >