Copy/Paste Value Between Different Workbooks

Question

I'm wanting to use VBA to copy a range of data from one workbook and paste it in another workbook. To know where to paste the information, I search for the next empty row.

The code works successfully until the last portion when trying to copypaste values. I do not get any errors, or any indication of success or failure. I can see it being copied correctly (the marching dots), and the correct cell is selected, but nothing is pasted.

Sub Button1_Click()
   Dim wb1 As Workbook
   Dim sht As Worksheet
   Dim rng As Range
   Dim databasewb As Workbook
   Dim databasesht As Worksheet
   Dim eRow As Integer

    'set workbooks to variables

    Set databasewb = Workbooks("Aged Debt Data V1.xlsm")

    Set wb1 = Workbooks.Open("C:\Users\roanderson\Desktop\Aged debt\Templates\BIO Inc (IO) Template.xlsx")


    'select sheet where data lies

    Set sht = wb1.Sheets("Conversion to aged debt format")
    sht.Activate

    'copy range on sheet

    Set rng = sht.Range("A2", Range("A2").End(xlDown).End(xlToRight))
    rng.Copy


    ' paste range into database

     'activate database workbook
    databasewb.Activate

    'find next empty row
    eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    MsgBox (eRow)

    'paste values into empty row
    Sheet1.Cells(eRow, 1).Select
    rng.PasteSpecial Paste:=xlPasteValues
    wb1.Close

End Sub

The data to be pasted in the Datebase workbook,

No need to `Activate` or `Select`. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jul 02 '19 at 16:00
Also, why are you using `rng.PasteSpecial`? `rng` is the range you *copied*, right? — BigBen, Jul 02 '19 at 16:00
Bypassing the clipboard is recommended for just pasting values. PasteSpecial is less efficient — Noam Brand, Nov 02 '21 at 13:58

pgSystemTester · Accepted Answer · 2019-07-02T17:01:41.223

When possible, try to avoid using Copy Paste with VBA, as well as avoid using select. Since you just want to copy values, using VBA's Value approach would likely be easier. Modify your line of code where you try to paste special to setting the value. See below

'paste values into empty row
Sheet1.Cells(eRow, 1).Resize(RNG.Rows.Count, RNG.Columns.Count).Value = RNG.Value
wb1.Close

What this is doing starting in Cells(erow,1) the code is using Resize to set the starting range to be the same number of rows and columns or your variable RNG. Then it's just setting the values, the same result as CopyPasteValue only less overhead.

However, if you did want to keep the approach of Copy paste value, then modify your code as such:

'paste values into empty row
Sheet1.Cells(eRow, 1).PasteSpecial Paste:=xlPasteValues
wb1.Close

Thankyou, PG, it's worked perfectly. i wouldntn't normally use copy and paste with VBA, however the job needed will be using around 10 different workbooks, all formatted abit differently and adding information to one workbook to represent across the business in tableau, i just felt automating the copy/paste part could save quite abit of time. Thankyou again — Ross Anderson, Jul 03 '19 at 08:29

score 0 · Answer 2 · edited Jul 03 '19 at 01:38

0

Change rng.pastespecial to selection.pastespecial

edited Jul 03 '19 at 01:38

Mark

934
1
10
25

answered Jul 02 '19 at 16:33

Hani

1

3

An explanation of *why* and *how* this helps solve the problem improves the quality and longevity of your answer. See ["How do I write a good answer?"](https://stackoverflow.com/help/how-to-answer). – slothiful Jul 02 '19 at 20:44

Noam Brand · Answer 3 · 2021-11-02T13:47:59.053

Performance improvement for copy-paste values. Modular sub.

Bypassing the clipboard is recommended for just Pasting Values. PasteSpecial is less efficient.

See section 8: https://techcommunity.microsoft.com/t5/excel/9-quick-tips-to-improve-your-vba-macro-performance/m-p/173687

Sub CopyPasteSingleCol pastes to PasteFirstRow for Single Column.

sub CopyPasteSingleCol2firstBlank pastes after last blank in column for Single Column.

Sub CopyPasteSingleCol(SrcSheet As Worksheet, ByVal SrcCol As String, ByVal SrcFirstRow As Long, _
                       PasteSheet As Worksheet, ByVal PasteCol As String, ByVal PasteFirstRow As Long)
    Dim SrcLastRow As Long
    Dim PasteLastrow As Long
    Dim SrcRng As Range
    Dim PasteRng As Range

    SrcLastRow = SrcSheet.Cells(SrcSheet.Rows.Count, SrcCol).End(xlUp).Row
   
    Set SrcRng = SrcSheet.Range(SrcCol & SrcFirstRow & ":" & SrcCol & SrcLastRow)
    Set PasteRng = PasteSheet.Range(PasteCol & PasteFirstRow)
   SrcRng.Copy PasteRng
End Sub


Sub CopyPasteSingleCol2firstBlank(SrcSheet As Worksheet, ByVal SrcCol As String, ByVal SrcFirstRow As Long, _
                                  PasteSheet As Worksheet, ByVal PasteCol As String)
    Dim SrcLastRow As Long
    Dim PasteLastrow As Long
    Dim SrcRng As Range
    Dim PasteRng As Range
    
        SrcLastRow = SrcSheet.Cells(SrcSheet.Rows.Count, SrcCol).End(xlUp).Row
        PasteLastrow = PasteSheet.Cells(PasteSheet.Rows.Count, PasteCol).End(xlUp).Row + 1
        ' If first row is empty there was not need to add +1 to Lastrow
        If PasteSheet.Cells(1, PasteCol) = vbNullString Then PasteLastrow = 1
        Set SrcRng = SrcSheet.Range(SrcCol & SrcFirstRow & ":" & SrcCol & SrcLastRow)
        Set PasteRng = PasteSheet.Range(PasteCol & PasteLastrow)

   SrcRng.Copy PasteRng
End Sub

Sub TESTCopyPasteSingleCol()
Dim SrcSheet As Worksheet
Dim PasteSheet As Worksheet

'Must qualify your Sheet by using Set before calling sub
Set SrcSheet = Workbooks("importGsheettoExcel3.xlsm").Worksheets("SH1")
Set PasteSheet = Workbooks("importGsheettoExcel.xlsm").Worksheets("SH2")

Call CopyPasteSingleCol(SrcSheet, "B", 2, _
                        PasteSheet, "G", 2)
End Sub

Sub TESTCopyPasteSingleCol2firstBlank()
Dim SrcSheet As Worksheet
Dim PasteSheet As Worksheet

'Must qualify your Sheet by using Set before calling sub
Set SrcSheet = Workbooks("importGsheettoExcel3.xlsm").Worksheets("SH1")
Set PasteSheet = Workbooks("importGsheettoExcel.xlsm").Worksheets("SH2")
Call CopyPasteSingleCol2firstBlank(SrcSheet, "B", 2, _
                                   PasteSheet, "G")
End Sub

Copy/Paste Value Between Different Workbooks

3 Answers3