By user2832896


2013-10-14 00:18:04 8 Comments

I have written the following code and continually see pastespecial method of class has failed. I have tried to overcome this issue, but nothing seems to work. I am trying to copy an entire sheet from one work book, and paste it into another:

Set x = Workbooks.Open(" path to copying book ")
Workbooks.Open(" path to copying book ").Activate
Range("A1").Select
'Cells.Select
Selection.Copy
Set y = Workbooks.Open("path to pasting book")
Workbooks.Open("Path to pasting book").Activate

With y
    Sheets("sheetname").Cells.Select
    Range("A1").PasteSpecial
    'Sheets("sheetname").PasteSpecial
    .Close
End With

With x
    .Close
End With

2 comments

@David Zemens 2013-10-14 01:03:16

This should do it, let me know if you have trouble with it:

Sub foo()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, copy what you want from x:
x.Sheets("name of copying sheet").Range("A1").Copy

'Now, paste to y worksheet:
y.Sheets("sheetname").Range("A1").PasteSpecial

'Close x:
x.Close

End Sub

Alternatively, you could just:

Sub foo2()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1") 

'Close x:
x.Close

End Sub

To extend this to the entire sheet:

With x.Sheets("name of copying sheet").UsedRange
    'Now, paste to y worksheet:
    y.Sheets("sheet name").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With

And yet another way, store the value as a variable and write the variable to the destination:

Sub foo3()
Dim x As Workbook
Dim y As Workbook
Dim vals as Variant

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Store the value in a variable:
vals = x.Sheets("name of sheet").Range("A1").Value

'Use the variable to assign a value to the other file/sheet:
y.Sheets("sheetname").Range("A1").Value = vals 

'Close x:
x.Close

End Sub

The last method above is usually the fastest for most applications, but do note that for very large datasets (100k rows) it's observed that the Clipboard actually outperforms the array dump:

Copy/PasteSpecial vs Range.Value = Range.Value

That said, there are other considerations than just speed, and it may be the case that the performance hit on a large dataset is worth the tradeoff, to avoid interacting with the Clipboard.

@user2832896 2013-10-14 01:46:15

David that you very much for your help! Unfortunately I am receiving a compound error saying "Method or data member not found" and it is highlighting the first ".Sheets" line from which it is trying to copy. Do you know what may be the problem?

@David Zemens 2013-10-14 02:49:51

The error happens at this: Sheets("name of copying sheet").Range("A1").Copy ?? Can you double-check that you've copied it correctly and it doesn't contain any typos? I can't think of any reason you'd get that error (even typos should raise a different error message) but try two alternative methods also added to my answer, above.

@user2832896 2013-10-14 04:07:16

I have used the second method you suggested and it works perfectly! thank you very much!

@user2832896 2013-10-14 07:47:24

David, how would I extend this equating of cells to equating entire sheets?

@David Zemens 2013-10-14 13:51:31

I added that info, above.

@user2832896 2013-10-15 00:22:27

Set x = Application.Workbooks.Open("xpath") Set y = Application.Workbooks.Open("ypath") With WorkbookCopyingtoo.Sheets("sheet").UsedRange WorkbookCopyingFrom.Sheets("Sheet").Range("A1").Resize(.Rows‌​.Count, Columns.Count) = .Value End With

@user2832896 2013-10-15 00:23:31

This is how I have it David and I am still getting errors saying subscript out of range. Can you see what the problem is?

@David Zemens 2013-10-15 01:51:12

As I said before the only thing I can think of that would cause that error on a range assignment is a worksheet name that doesn't exist. You're on your own. Now, since I've answered this particular problem for you, please consider marking the answer as "accepted", if you're having trouble with another error (i.e., your subscript out of range error), please post a new question with that problem.

@MalTec 2014-03-12 19:22:22

@DavidZemens how to copy from a workbook to new workbook for specific range? Set endUserWS = Workbooks.Add Copy Columns 1 by 1 i = 0 For Each col In colNames On Error GoTo colNotFound colN = destWS.Rows(1).Find(col, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column If colN <> -1 Then endUserWS.Sheets(1).Range(Cells(2, i)).Value = destWS.Range(Cells(2, colN), Cells(Cells(Rows.Count, colN).End(xlUp).Row, colN)) End If i = i + 1 Next col is it fine? I get blank cells in new workbook. Values dont get copied.

@Brian 2016-04-17 13:47:55

the code you suggested works but i get a popup saying" there is a large amount of information on the clipboard,..." how do i avoid getting this popup

@David Zemens 2016-04-17 14:03:13

@Brian is that happening when closing a workbook?

@David Zemens 2016-04-17 14:04:35

Use Application.DisplayAlerts=False before closing the file (and remember to reset it to True after closing the file)

@L42 2013-10-14 03:22:20

You copied using Cells.
If so, no need to PasteSpecial since you are copying data at exactly the same format.
Here's your code with some fixes.

Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("path to copying book")
Set y = Workbooks.Open("path to pasting book")

Set ws1 = x.Sheets("Sheet you want to copy from")
Set ws2 = y.Sheets("Sheet you want to copy to")

ws1.Cells.Copy ws2.cells
y.Close True
x.Close False

If however you really want to paste special, use a dynamic Range("Address") to copy from.
Like this:

ws1.Range("Address").Copy: ws2.Range("A1").PasteSpecial xlPasteValues
y.Close True
x.Close False

Take note of the : colon after the .Copy which is a Statement Separating character.
Using Object.PasteSpecial requires to be executed in a new line.
Hope this gets you going.

Related Questions

Sponsored Content

1 Answered Questions

3 Answered Questions

[SOLVED] macro error when copying print area and pasting to another sheet

  • 2017-05-01 19:57:42
  • peterreed
  • 77 View
  • 2 Score
  • 3 Answer
  • Tags:   excel vba excel-vba

2 Answered Questions

[SOLVED] Copy worksheet from another workbook including charts

3 Answered Questions

[SOLVED] VBA - annoying copy and paste issue

  • 2017-04-07 15:32:32
  • Statsanalyst
  • 769 View
  • 0 Score
  • 3 Answer
  • Tags:   vba excel-vba excel

2 Answered Questions

2 Answered Questions

[SOLVED] Copying range and pasting into new workbook

  • 2012-09-09 16:14:46
  • CaptainProg
  • 76288 View
  • 6 Score
  • 2 Answer
  • Tags:   excel-vba vba excel

1 Answered Questions

2 Answered Questions

[SOLVED] Excel vba Shape Paste not work

  • 2015-03-16 09:49:01
  • Dmitrij Holkin
  • 519 View
  • 1 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

1 Answered Questions

[SOLVED] Copy and Paste Issue

  • 2013-05-10 19:09:52
  • user2371229
  • 107 View
  • 1 Score
  • 1 Answer
  • Tags:   excel-vba vba excel

Sponsored Content