Remember that all_data.xls has to be open before you start the macro. Is it?
This version will yell at you if you don't have it open (remember to change the
sheet name to what you want).
Option Explicit
Sub Tester1()
Dim wb1 As Workbook
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If
Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.Cells(2, 1).End(xlDown))
For Each cell In rng
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
Set rng1 = sh.Range(sh.Cells(2, "H"), sh.Cells(2, "H").End(xlDown))
res = Application.Match(CLng(cell.Offset(0, 7)), rng1, 0)
If IsError(res) Then
cell.EntireRow.Copy _
Destination:=rng1.Offset( _
rng1.Rows.Count, 0).Resize(1, 1).EntireRow.Cells(1)
End If
Next cell
End Sub
Yes the test macro seems to work because there is no error message for the
"all_data.xls" file. But the other file "Reports.xls" which contains the
general macro and is to retrive data from "all_data.xls" ...when this is
runned it generates the "subscript out of range" error message.
