It is easier to go through every sheet in book2 and copy the rows that don't
match the accounts in book1 sheet1. I assumed the account numbers are in
column A. change code as required.


Sub FindMissAccounts()

Set LookupSht = Workbooks("Book1.xls").Sheets("Sheet1")
Set DestupSht = Workbooks("Book1.xls").Sheets("Sheet2")
Set AccountBk = Workbooks("Book2.xls")

NewRow = 1
For Each sht In AccountBk.Sheets
With sht
RowCount = 1
Do While .Range("A" & RowCount) < ""
Account = .Range("A" & RowCount)
Set c = LookupSht.Columns("A").Find(what:=Account, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=DestupSht.Rows(NewRow)
NewRow = NewRow + 1
End If
RowCount = RowCount + 1
Loop
End With

Next sht

End Sub


"akemeny" wrote:

What I need to do is fairly simple yet I can't find anything that will allow
me to do it. I have two workbooks. In workbook1 I have almost 20 worksheets
with account information. In workbook2 I have two worksheets, one blank.

What I want to do is create either a macro or a function that will look at
the account sheet in workbook2, then look at ALL the sheets in workbook1 and
find the accounts from workbook2 that do not appear on any of the sheets in
workbook1. The accounts from workbook2 that do not appear in workbook1 I
need copied to the blank sheet in workbook2.

Is this possible?

Any help would be greatly appreciated.