Compile error

[Jim May]

I'm wanting to Copy some auto-filtered data to
worksheets("POReqsSent") and to
worksheets("POReqsHistoy") << of the same current Workbook
PLUS I ant to copy it to a Closed Workbook
on the same drive
workbbook("POs-ExecDir.xls").Worksheets("POReqsIncoming").Range("A2")

Right now my code referring to the closed workbook is (above mention and
below stated Is BOMBING!!
Can some one assist?


.... Code before...
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No Data found to copy"
Exit Sub
Else
Set rng1 = ActiveSheet.AutoFilter.Range
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
Destination:=Worksheets("POReqsSent").Range("A2")
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
Destination:=Worksheets("POReqsHistory").Range("A2")
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
Destination:="\\MyPc1\Shareddocs\\POs-ExecDir.xls\Worksheets("POReqsIncoming").Range("A2")"
End If
rng2.EntireRow.Delete
.... code following...

 

[Tom Ogilvy]

You can't copy to a closed workbook. You basically can't write to a closed
workbook unless you want to treat it as a database and use something like
ADO to update the "table"

Easiest is just to open it, write to it, save it, close it.

 

[Jim May]

Tom:
As you suggested I've tried to Open, Write, Save and Close the ExecWB
But I've still got a problem.. Can you see it?
Tks,
Jim

Else
Set rng1 = ActiveSheet.AutoFilter.Range
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
Destination:=Worksheets("POReqsSent").Range("A2")
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
Destination:=Worksheets("POReqsHistory").Range("A2")
ExecWB = Workbooks.Open("C:\Documents and Settings\Jim May\My
Documents\Art_Museum\Pos-ExecDir.xls")
rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
<<<< Code is Bombing here !!! << No Clue Why!!
Destination:=ExecWB.sheets("Incoming").Close SaveChanges:=True
End If

 

[Tom Ogilvy]

ExecWB = Workbooks.Open("C:\Documents and Settings\Jim May\My
Documents\Art_Museum\Pos-ExecDir.xls")

should be

set ExecWB = Workbooks.Open("C:\Documents and Settings\Jim May\My
Documents\Art_Museum\Pos-ExecDir.xls")

rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1).Copy _
Destination:=ExecWB.sheets("Incoming")

ExecWB.Close SaveChanges:=True

 

[Jim May]

I got it going AND/PLUS learned
an important lesson I should have
already known; but anyway,
Thanks for your patience and continued
Contribution to this group;
Jim