MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying/Pasting worksheet data from one workbook to another


Posted by Gail on August 15, 2001 3:49 PM

With code, I can copy data, Sheets(1).Cells.Range("A1:K1").Copy from Workbook #1, but
when I go to ActiveCell (i.e. A7) in Workbook #2 where I want to paste and use ActiveSheet.Paste, the paste fails. I think I've tried everything I can think of with no luck. This seems like such a simple task. I need to do this in code.


Posted by Robb on August 15, 2001 5:15 PM

Gail

Are you activating the worksheet in Workbook 2 first? Also, is the worksheet protected? Other than that, I can see no reason for the paste to fail. Perhaps you could post the actual code you are using.

Regards

Posted by Gail on August 16, 2001 8:04 AM

Wkbk #1 & 2 are open. I copy the cells (range A1:J1)from wkbk #1, then close wkbk #1. I go to active cell, A7 in wkbk 2 and use Activesheet.Paste and it fails. It fails when I use code to attempt this. If I turn on the macro recorder and just do the steps from the user side, it works fine! I even used the macro code to generate the programming code and then it failed. The code I am using is as follows:

Workbooks.Open FileName:=BenCheck
Sheets(1).Cells.Range("A1:K1").Activate
Sheets(1).Cells.Range("A1:K1").Copy
ActiveWorkbook.Close ' wkbk #1
Range(ActiveCell, ActiveCell.Offset(0, 9)).Select ' in wkbk #2
ActiveSheet.Paste

thank you for any assistance you can provide! G

: With code, I can copy data, Sheets(1).Cells.Range("A1:K1").Copy from Workbook #1, but : when I go to ActiveCell (i.e. A7) in Workbook #2 where I want to paste and use ActiveSheet.Paste, the paste fails. I think I've tried everything I can think of with no luck. This seems like such a simple task. I need to do this in code.

Posted by Robb on August 17, 2001 4:26 AM

Gail

I am able to get it to fail only when the Worksheet is protected - other than that it seems to work fine.

The only other thing I can see is the file name in the Workbooks Open statement - if you are using this to open one of the workbooks, it needs "" ( this would probably trigger a different error though).

Is the sheet protected? If not, which Workbook contains the code - the one being copied from or the one being copied to?

Regards

Sheets(1).Cells.Range("A1:K1").Activate Sheets(1).Cells.Range("A1:K1").Copy ActiveWorkbook.Close ' wkbk #1 Range(ActiveCell, ActiveCell.Offset(0, 9)).Select ' in wkbk #2 ActiveSheet.Paste

: Gail : Are you activating the worksheet in Workbook 2 first? Also, is the worksheet protected? Other than that, I can see no reason for the paste to fail. Perhaps you could post the actual code you are using. : Regards :