CODEWITHSUNDEEP
vbaexcel
Gaus Shaikh
Gaus Shaikh

Reputation: 209

VBA to copy and past in two different workbooks

I want to copy all the data and paste it in new workbook. With the below coding, I am able to paste all the values but it is creating two workbooks and pasting the data in one workbook.

I want to create only one new workbook and past the data. Not sure as to what went wrong. 

On Error Resume Next
ThisWorkbook.Sheets(2).Copy
Dim wkb As Workbook
Set wkb = Workbooks.Add
wkb(1).PasteSpecial xlPasteValues
On Error GoTo 0

Upvotes: 0

Views: 50

Answers (2)

AlexP
AlexP

Reputation: 4430

The Copy method has a different meaning when applied to a Worksheet and when applied to a Range:

  • The Copy method of a Worksheet creates a copy of the sheet; in the absence of any parameter, the copy is placed in a new workbook.

  • The Copy method of a Range puts a copy of the Range on the clipboard, from where you can then Paste it somewhere else.

So in your case, the statement

ThisWorkbook.Sheets(2).Copy

already makes a copy the Worksheet into a new workbook.

If you want to create the new workbook explicitly then you should copy the used range to the clipboard:

ThisWorkbook.Sheets(2).UsedRange.Copy

Upvotes: 2

user6698332
user6698332

Reputation: 427

It's not the answer, but let me do it instead of yourself: 

On Error Resume Next 
    ThisWorkbook.Sheets(2).Copy 
    Dim wkb As Workbook 
    Set wkb = Workbooks.Add 
    wkb(1).PasteSpecial xlPasteValues 
    On Error GoTo 0

.

Upvotes: 0

Related Questions