Copy and Paste Between 2 Workbooks

**sbnjac80** · 07-01-2009, 06:02 PM

Hi all -

I am trying to open a workbook called 'Dispfri1,"copy some data, then paste into another workbook, called "Test Spreadsheet," using VBA. The problem is, the code I have below is not working. Any advice? Thanks.

Sub hi()
    Workbooks.Open Filename:="D:\Documents and Settings\03340496\Desktop\SBN\Dispfri1.xls"
    Sheets("SCHED").Select
    
    Range("A51:C59").Select
    Selection.Copy
    
    Windows("Test Spreadsheet.xls").Activate
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F12").Select
    
   
End Sub

**JBeaucaire** · 07-01-2009, 06:37 PM

Hi and welcome to the forum. Can you EDIT that first post, then click on GO ADVANCED, highlight all that code, then click the # icon to wrap it in code tags? It's a forum rule and a good one, makes things easier for everyone viewing the threads.

Here's a little shorter version of your code:

Sub hi()

Workbooks.Open Filename:="D:\Documents and Settings\03340496\Desktop\SBN\Dispfri1.xls"
Sheets("SCHED").Select
Range("A51:C59").Copy

Workbooks("Test Spreadsheet.xls").Sheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
Range("F12").Select

End Sub

If you go into the VBEditor and use F8 to step through your original code one line at a time, where does it fail?

Does my version fail in the same place?

Does the spot where it fails help you to see what the problem might be?

**sbnjac80** · 07-02-2009, 10:13 AM

Thanks for your help.

Your code does not fail until the very last line:

Range("F12").Select

But the macro is not performing like it's supposed to. It's almost like it is copying from "Test Spreadsheet", which is blank. When the macro copies, it is supposed to contain the data in Dispfri1, but it's completely blank.

**JBeaucaire** · 07-02-2009, 10:22 AM

Interesting. As you F8 through the formula, can you see:

1) The Dispfri1 sheet open?
2) The data get highlighted and copied?

I can't imagine how a macro would fail on a simple "select" line, unless that cell doesn't exist. Very odd.

**sbnjac80** · 07-02-2009, 10:46 AM

The Dispfri is opening, but it's not copying the range. I think it has something to do with this: when I copy the data I need from Dispfri and paste special into another spreadsheet (without using a macro), I get this dialog box (see attachment), but copying the data into Test Spreadsheet it is not giving me that dialog box.

**JBeaucaire** · 07-02-2009, 11:17 AM

No way to know from here why you get/need that dialog box at all. I cut/copy/paste sheets from book to book with VBA every day, don't think I've ever seen that come up.