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...

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.

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:OTD%e.125174$Ep.101153@lakeread02...
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:
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:OTD%e.125174$Ep.101153@lakeread02...
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...

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


--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:3UI%e.125192$Ep.45754@lakeread02...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:OTD%e.125174$Ep.101153@lakeread02...
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...

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


"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:3UI%e.125192$Ep.45754@lakeread02...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:OTD%e.125174$Ep.101153@lakeread02...
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...