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
Jack wrote:
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.
"Dave Peterson" wrote in message
...
Before you send anything, what's the name of the worksheet?
And try this:
Open your workbook (all_data.xls--that is the correct name for the
workbook???)
then create a new test macro:
option explicit
sub test01()
workbooks("all_data.xls").worksheets("whateveryouc alledit").select
end sub
And post back what happens.
Jack wrote:
Hi Dave,
Yes it looks like the name matches the worksheet name. Would it be a
good
idea if I email you the two files and see if you can work it out?. If
so,
please let me know which email add. I should use...
Sincerely
J.
"Dave Peterson" wrote in message
...
And you're sure that name matches a worksheet name in wb2?
Maybe leading/trailing/extra spaces????
Jack wrote:
Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks
"Dave Peterson" wrote in message
...
put this line right before the offending line:
msgbox "***" & cell.offset(0,1).value & "***"
Do you see what you expected between the ***'s?
Jack wrote:
I think you were correct with your assumption on worksheet names
but
although am sure about the one I tried, I also did get a
"subscript
out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.
"Myrna Larson" wrote in
message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.
On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"
wrote:
Thanks Dave,
but I keep getting a "subscript out of range" error from the
compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn
"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok
for
me:
Option Explicit
Sub Tester1()
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 wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
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
But you'll have to adjust the name of the worksheet that
contains
the
names--I
used Sheet1.
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson