Excel Macro won't paste to other workbook

[nestarush]

I am trying to copy cells from one sheet, open a different existing workbook, and paste the data...

Cells.Select
Selection.Copy

Workbooks.Open "C:\Macro Testing\copyto.xlsx"

Cells.Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Save
ActiveWindow.Close

It copies the sheet, opens the other workbook, but doesn't paste. The message "select destination and press enter or choose paste" is at the bottom of copyto.xlsx. If I hit enter it will paste, but will not run the rest of the macro. Same problem when I try to copy a specifc range and paste to a specifc range.

How do I get it to just paste on it's own?

Thanks for your help!

 

[Gavona]

Try stepping through your code and watching what is going on.
Does Cells.select do as you expect?

It strikes me that you are not specifying what sheet to paste into - which could give unexpected results.
Also you say "it copies the sheet". If that is what you are trying to do then maybe right-click the sheet tab and choose "move or copy"....

Gavin

 

[nestarush]

Thanks for the reply.

Cells.select does copy the sheet.

It opens the workbook I want it to and even if I activate "Sheet 1" in the code, it still won't paste unless I hit enter.

 

[Gavona]

Copies the sheet or copies all the cells in the sheet?
Perhaps I am being pedantic.

Macro recorder shows me that
Selection.PasteSpecial Paste:=xlPasteValues works.

You don't need to select though:

    Cells.Copy
    Workbooks.Open Filename:="testloop.xls"
    Cells.PasteSpecial Paste:=xlPasteValues

Gavin

 

[nestarush]

I have the same issue with that code. It's like I have security setting stopping the macro from pasting on it's own.

If I open a new worbook, it pastes fine. It only has this problem when I open an existing workbook.


I have found many macros on the net that people have used, but on my computer, they always stop in the same place saying, "select destination and press enter...".

 

[Gavona]

Just that workbook or any workbook?
What version of Excel are you using?
Are both workbooks created using same version of excel?
I would expect a different message but source and destination may be different sizes - try
Cells(1,1).pastespecial....
Come to that do you really need to copy all cells?
Try UsedRange.copy maybe



Gavin

 

[nestarush]

Excel 2007. I made both workbooks today just trying to get it to work.

If I do a specific cell or specific range I have the same problem.

 

[Gavona]

Must be an Excel 2007 thing I suppose. Sorry I couldn't help.

Gavin

 

[nestarush]

I think I figured it out. When the new workbook opens, it stops the macro. The data still remaining on the clipboard causes the "select destination and press enter..." message.

So I need to have a macro on the 2nd workbook that starts up when it is opened.

 

[DaveInIowa]

Excel's pretty tempermental when it comes to retaining any copied data. To see this in action, select some cells and copy them, press Escape to get rid of the "marching ants", then try to paste into Notepad. You'll see that the data wasn't actually copied to the clipboard. Certain functions have the same effect as pressing the Escape key; they "knock-out" the pending copy function. I suspect that's what's happening in your macro.

In general, you should avoid Selecting cells to perform operations on them. Here's your initial example reworked to avoid selecting anything.

Dim rg As Range

Set rg = Cells

Workbooks.Open "C:\Macro Testing\copyto.xlsx"

rg.Copy

ActiveSheet.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Save
ActiveWindow.Close