Searching mutiple sheets for a value in one column

[Wester]

Hi,

I am trying to write a macro that will copy row data from a 'master' sheet
to multiple sheets in workbook. I can't figure out the function to look at a
specific column in each worksheet and compare that data value to my 'master'
sheet.

Here is my example. My 'master' sheet has quantity counts of apples,
oranges and bananas from each day. I want to copy my 'apple' rows to the
'Apple Counts' worksheet, and by 'orange' rows to my 'Orange Counts'
worksheet and etc. So I am needing help with the function that could look at
my entire workbook or each worksheet, comparing the value of my "Fruit"
column in my master sheet to the same column in each worksheet. If found,
then the row is moved to that worksheet. Hope that makes sense?

Any help would be much appreciated.

-Wesley

 

[Charles Chickering]

Ok I'm not sure exactly what you're looking for but I'll give it a shot.
Dim ws as worksheet
Dim rSearch as range
set rSearch = Range("A1") 'I'm guessing you have a header telling _
what fruit to look for
For each ws in activeworkbook.worksheets
if ws.name <> "Master" Then
'We're not on the master so check value
if ws.range("A1") = rsearch then
'Do your coping here
rSearch.Offset(1).EntireRow.Copy _
ws.Range("A1").End(xlDown).Offset(1)
End if
end if
Next

 

[Wester]

Hi Charles,

Thanks for your reply! I have to admit that I am a "neophyte" when it comes
to VBA. I tried working your with your macro but it generated an error.
Then I added a "On Error Resume Next" statement before "For each ws in
activeworkbook.worksheets" then it started to work but it just copied my
first row of data in my 'master' sheet to all sheets. So I am not sure what
needs to be changed.

Not sure how good you are with VBA, but here is another Macro I found that
almost does exaclty what I need. It does a bunch of worksheet name checking
and creation which I don't need. Unfortunately, I can't figure out how to
modify it to take the "CurrentCellValue" in the "master" sheet and look for
that value in all worksheets in my workbook.

Got any solutions?

Thanks!

-Wesley


Sub CopyRowsToSheets()

'copy rows to worksheets based on value in column A

'assume the worksheet name to paste to is the value in Col A

Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String

'start with cell A2 on "Master" sheet
Set CurrentCell = Worksheets("Master").Cells(2, 1) 'row ... column ...

Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
Set SourceRow = CurrentCell.EntireRow

'Check if worksheet exists
On Error Resume Next

Testwksht = Worksheets(CurrentCellValue).Name

If Err.Number = 0 Then

'MsgBox CurrentCellValue & " worksheet Exists"
Else

MsgBox "Adding a new worksheet for " & CurrentCellValue

Worksheets.Add.Name = CurrentCellValue

End If

On Error GoTo 0 'reset on error to trap errors again

Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)

' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1
SourceRow.Copy Destination:=Targetsht.Cells(TargetRow, 1)

'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)

Loop

End Sub