VBA Excel Copy Values instead of Function without .PasteSpecial

Question

I'm trying to copy a range of cells from one data file (sh1 as Worksheet) to another (sho as Worksheet). The cells should be pasted beneath the existing data. Since the amount of cells to copy and the amount of existing data varies. I created this code:

    Dim sh1 As Worksheet, sho As Worksheet, lr As Long, rng1 As Range
    lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng1 = sh1.Range("A3:B" & lr)
    rng1.Copy sho.Cells(Rows.Count, 1).End(xlUp)(2)

This works perfectly fine - but it copies the functions in the Worksheet sh1 to the Worksheet sho and not the values. I know that the code for this is ".PasteSpecial" but I'm not able to match it in my code, without destroying the other prerequisites.

Answer 1

If you have Sheet1 and Sheet2 please try the below:

Option Explicit


Sub test()

   Dim sh1 As Worksheet, sho As Worksheet, lr As Long, rng1 As Range

   Set sh1 = Sheet1
   Set sho = Sheet2

    lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng1 = sh1.Range("A3:B" & lr)
    rng1.Copy
    sho.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues



End Sub

Answer 2

Instead of:

rng1.Copy sho.Cells(Rows.Count, 1).End(xlUp)(2)

Assign the values directly. It avoids the clipbaord and thus it is quicker:

sho.Cells(sho.Rows.Count, 1).End(xlUp).Offset(2).Resize(rng1.rows,rng1.columns).Value = rng1.Value

Answer 3

You can use this code to copy the value of A1 to B1 (if A1 is a formula)

Range("B1").Formula = Range("A1")