| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't paste the second time around
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't paste the second time around
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" 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't paste the second time around
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" wrote in message ... 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" 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't paste the second time around
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" 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" wrote in message ... 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" 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't paste the second time around
Jim,
I got it. Thanks!! Jeff "Jim Cone" wrote in message ... 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" 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" wrote in message ... 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste transpose and link at the same time | Excel Worksheet Functions | |||
| Copy and Paste to multiple woorksheets at one time | Excel Discussion (Misc queries) | |||
| Copy and Paste only working for one cell at a time | Excel Discussion (Misc queries) | |||
| Can you paste values on multiple worksheets at one time? | Excel Worksheet Functions | |||
| Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming | |||
![Jeff Wright[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
