pulling data from multiple sheet on to one sheet with specific value

[Purplerose04]

Hi,

I was wondering if anyone could help me. I'm trying to figure this out for my work place. I'm a newbie to VBA, so I don't know how to do this. But I need to pull the information from multiple sheets with the Services "OIC Accepted" into the Master sheet. I need it to be auto-updated since the Sheets will be changing. If anyone can help, it would be great.. I'm trying to figure this out for weeks and unfortunately, I didn't study VBA long enough to understand how to do it.

Thank you

[patel]

[VBA]Sub OICTransfer()
drow = Cells(Rows.Count, "B").End(xlUp).Row + 1
For Each sh In Sheets
If sh.Name <> "ACCEPTED OIC's" Then
LR = sh.Cells(Rows.Count, "F").End(xlUp).Row
For j = 11 To LR
If sh.Cells(j, "F") = "OIC - ACCEPTED" Then
sh.Range("B" & j & ":E" & j).Copy Cells(drow, 2)
drow = drow + 1
End If
Next
End If
Next

End Sub[/VBA]

[Purplerose04]

Wow that work awesome. However, is there any way for it to be auto-update like if I was to modified something, can it also update on the master sheet? Also in the Tax Pro column to get the name from each sheet? Does it matter if it was a dropdown box?

[patel]

[vba]Sub OICTransfer()
drow = Cells(Rows.Count, "B").End(xlUp).Row + 1
For Each sh In Sheets
If sh.Name <> "ACCEPTED OIC's" Then
LR = sh.Cells(Rows.Count, "F").End(xlUp).Row
For j = 11 To LR
If sh.Cells(j, "F") = "OIC - ACCEPTED" Then
sh.Range("B" & j & ":E" & j).Copy Cells(drow, 2)
range("F" & drow) = sh.name
drow = drow + 1
End If
Next
End If
Next

End Sub[/vba]