Can't paste the second time around

[Jeff Wright]

Good morning!

Well, I'm really stumped by this one. The macro below simply grabs data from
a worksheet in one workbook, and pastes it to a worksheet in another
workbook. If I run it once, it works fine. If I immediately then run it a
second time, I get an error "Paste method of worksheet class failed." Now,
the interesting point is that if I delete the two lines which unprotect and
protect the sheet, the macro will run fine every time. I don't get it. Can
anyone help me on this?

Thanks,

Jeff
Tucson, Arizona

Sub GetData()
'NOTE: This macro is run from "MainProgram.xls"
Workbooks.Open ("abctest.xls")
Windows("abctest.xls").Activate
Worksheets("Save Drop Locations").Select
Range("A1:D1").Select
Selection.Copy
Windows("MainProgram.xls").Activate
Worksheets("Drop Locations").Select
ActiveSheet.Unprotect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Protect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Application.CutCopyMode = False
Windows("abctest.xls").Activate
ActiveWorkbook.Close
Windows("MainProgram.xls").Activate
End Sub

[Jim Cone]

Hi Jeff,

I assume the "copy" is being lost.
Give this untested version a try...
'----------------------------------
Sub GetData()
Workbooks.Open ("abctest.xls")
With Workbooks("MainProgram.xls").Worksheets("Drop Locations")
.Unprotect
Workbooks("abctest.xls").Worksheets _
("Save Drop Locations").Range("A1:D1").Copy
.Range("A2").Paste
.Protect
Application.CutCopyMode = False
Workbooks("abctest.xls").Close SaveChanges:=False
.Activate
End With
End Sub
'-----------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jeff Wright" <[email protected]> wrote in message
news:1x5Je.67789$4o.28928@fed1read06...
Good morning!

Well, I'm really stumped by this one. The macro below simply grabs data from
a worksheet in one workbook, and pastes it to a worksheet in another
workbook. If I run it once, it works fine. If I immediately then run it a
second time, I get an error "Paste method of worksheet class failed." Now,
the interesting point is that if I delete the two lines which unprotect and
protect the sheet, the macro will run fine every time. I don't get it. Can
anyone help me on this?
Thanks,
Jeff
Tucson, Arizona

Sub GetData()
'NOTE: This macro is run from "MainProgram.xls"
Workbooks.Open ("abctest.xls")
Windows("abctest.xls").Activate
Worksheets("Save Drop Locations").Select
Range("A1:D1").Select
Selection.Copy
Windows("MainProgram.xls").Activate
Worksheets("Drop Locations").Select
ActiveSheet.Unprotect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Protect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Application.CutCopyMode = False
Windows("abctest.xls").Activate
ActiveWorkbook.Close
Windows("MainProgram.xls").Activate
End Sub

[Jeff Wright]

Hi Jim,

Yes, this works! Is this because of the line which contains "Close
SaveChanges = False"?? If so, what does this line actually do? Does it free
a buffer for copying the same data? Really curious.

In any event, thanks again for your help, Jim. I just may be able to make my
Monday deadline.

Jeff
Tucson, Arizona

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
Hi Jeff,

I assume the "copy" is being lost.
Give this untested version a try...
'----------------------------------
Sub GetData()
Workbooks.Open ("abctest.xls")
With Workbooks("MainProgram.xls").Worksheets("Drop Locations")
.Unprotect
Workbooks("abctest.xls").Worksheets _
("Save Drop Locations").Range("A1:D1").Copy
.Range("A2").Paste
.Protect
Application.CutCopyMode = False
Workbooks("abctest.xls").Close SaveChanges:=False
.Activate
End With
End Sub
'-----------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jeff Wright" <[email protected]> wrote in message
news:1x5Je.67789$4o.28928@fed1read06...
Good morning!

Well, I'm really stumped by this one. The macro below simply grabs data from
a worksheet in one workbook, and pastes it to a worksheet in another
workbook. If I run it once, it works fine. If I immediately then run it a
second time, I get an error "Paste method of worksheet class failed." Now,
the interesting point is that if I delete the two lines which unprotect and
protect the sheet, the macro will run fine every time. I don't get it. Can
anyone help me on this?
Thanks,
Jeff
Tucson, Arizona

Sub GetData()
'NOTE: This macro is run from "MainProgram.xls"
Workbooks.Open ("abctest.xls")
Windows("abctest.xls").Activate
Worksheets("Save Drop Locations").Select
Range("A1:D1").Select
Selection.Copy
Windows("MainProgram.xls").Activate
Worksheets("Drop Locations").Select
ActiveSheet.Unprotect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Protect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Application.CutCopyMode = False
Windows("abctest.xls").Activate
ActiveWorkbook.Close
Windows("MainProgram.xls").Activate
End Sub

[Jim Cone]

Jeff,
In the revised code, the paste operation immediately follows the copy
method. Excel can/will "drop" items copied to the clipboard if
intervening operations take place.

The "SaveChanges:=False" tells Excel not to save the workbook
even if change have been made to it. It prevents the "Do you want
to save changes" message box from appearing.
Regards,
Jim Cone



"Jeff Wright" <[email protected]> wrote in message
news:SJ6Je.68248$4o.29113@fed1read06...
Hi Jim,
Yes, this works! Is this because of the line which contains "Close
SaveChanges = False"?? If so, what does this line actually do? Does it free
a buffer for copying the same data? Really curious.
In any event, thanks again for your help, Jim. I just may be able to make my
Monday deadline.
Jeff
Tucson, Arizona


"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
Hi Jeff,
I assume the "copy" is being lost.
Give this untested version a try...
'----------------------------------
Sub GetData()
Workbooks.Open ("abctest.xls")
With Workbooks("MainProgram.xls").Worksheets("Drop Locations")
.Unprotect
Workbooks("abctest.xls").Worksheets _
("Save Drop Locations").Range("A1:D1").Copy
.Range("A2").Paste
.Protect
Application.CutCopyMode = False
Workbooks("abctest.xls").Close SaveChanges:=False
.Activate
End With
End Sub
'-----------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jeff Wright" <[email protected]> wrote in message
news:1x5Je.67789$4o.28928@fed1read06...
Good morning!
Well, I'm really stumped by this one. The macro below simply grabs data from
a worksheet in one workbook, and pastes it to a worksheet in another
workbook. If I run it once, it works fine. If I immediately then run it a
second time, I get an error "Paste method of worksheet class failed." Now,
the interesting point is that if I delete the two lines which unprotect and
protect the sheet, the macro will run fine every time. I don't get it. Can
anyone help me on this?
Thanks,
Jeff
Tucson, Arizona

Sub GetData()
'NOTE: This macro is run from "MainProgram.xls"
Workbooks.Open ("abctest.xls")
Windows("abctest.xls").Activate
Worksheets("Save Drop Locations").Select
Range("A1:D1").Select
Selection.Copy
Windows("MainProgram.xls").Activate
Worksheets("Drop Locations").Select
ActiveSheet.Unprotect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Protect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Application.CutCopyMode = False
Windows("abctest.xls").Activate
ActiveWorkbook.Close
Windows("MainProgram.xls").Activate
End Sub

[Jeff Wright]

Jim,

I got it. Thanks!!

Jeff

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
Jeff,
In the revised code, the paste operation immediately follows the copy
method. Excel can/will "drop" items copied to the clipboard if
intervening operations take place.

The "SaveChanges:=False" tells Excel not to save the workbook
even if change have been made to it. It prevents the "Do you want
to save changes" message box from appearing.
Regards,
Jim Cone



"Jeff Wright" <[email protected]> wrote in message
news:SJ6Je.68248$4o.29113@fed1read06...
Hi Jim,
Yes, this works! Is this because of the line which contains "Close
SaveChanges = False"?? If so, what does this line actually do? Does it free
a buffer for copying the same data? Really curious.
In any event, thanks again for your help, Jim. I just may be able to make my
Monday deadline.
Jeff
Tucson, Arizona


"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
Hi Jeff,
I assume the "copy" is being lost.
Give this untested version a try...
'----------------------------------
Sub GetData()
Workbooks.Open ("abctest.xls")
With Workbooks("MainProgram.xls").Worksheets("Drop Locations")
.Unprotect
Workbooks("abctest.xls").Worksheets _
("Save Drop Locations").Range("A1:D1").Copy
.Range("A2").Paste
.Protect
Application.CutCopyMode = False
Workbooks("abctest.xls").Close SaveChanges:=False
.Activate
End With
End Sub
'-----------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jeff Wright" <[email protected]> wrote in message
news:1x5Je.67789$4o.28928@fed1read06...
Good morning!
Well, I'm really stumped by this one. The macro below simply grabs data from
a worksheet in one workbook, and pastes it to a worksheet in another
workbook. If I run it once, it works fine. If I immediately then run it a
second time, I get an error "Paste method of worksheet class failed." Now,
the interesting point is that if I delete the two lines which unprotect and
protect the sheet, the macro will run fine every time. I don't get it. Can
anyone help me on this?
Thanks,
Jeff
Tucson, Arizona

Sub GetData()
'NOTE: This macro is run from "MainProgram.xls"
Workbooks.Open ("abctest.xls")
Windows("abctest.xls").Activate
Worksheets("Save Drop Locations").Select
Range("A1:D1").Select
Selection.Copy
Windows("MainProgram.xls").Activate
Worksheets("Drop Locations").Select
ActiveSheet.Unprotect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.Protect 'ROUTINE RUNS FINE WHEN THIS LINE DELETED
Application.CutCopyMode = False
Windows("abctest.xls").Activate
ActiveWorkbook.Close
Windows("MainProgram.xls").Activate
End Sub