Copy from one workbook and paste into another

Question

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 workbook  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

User · Answer

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

User · Answer

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

[excel] Copy from one workbook and paste into another

Examples related to excel

Examples related to vba