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,

Answer 1

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