Excel VBA: Copy & Paste - Cells are being copied but not pasted

Question

I am brand new to VBA and am working on writing some code that copies data from a column in one workbook and pastes the data into another.

See my code below:

Set src = Workbooks.Open("C:\Users\RP\Desktop\CopyFrom.xlsm", True, True)

Dim startRow As Integer, endRow as Integer
With src.Sheets("Sheet1")
    startRow = .Range("B:B").Find(what:="*", after:=.Range("B1")).Row
    endRow = .Range("B:B").Find(what:="", after:=.Range("B1"), searchdirection:=xlPrevious).Row
End With

src.Worksheets("Sheet1").Range("B" & startRow & ":B" & endRow).Copy
Sheets("PasteHere").Range("B1").PasteSpecial xlPasteValues

I have startRow and startRow since the data is in Column Bbut does not start in cell B1.

When I run the macro, I see that the data from the src file is copied (it has the dotted "ants" around it and I can paste it wherever I please). However, in my current workbook, there is no data pasted.

Can you help me figure this out? Thank you!

Answer 1

As was commented you don't need to copy and paste if you just need the values; in fact copy and paste is much slower than working with the values directly!

Set src = Workbooks.Open("C:\Users\RP\Desktop\CopyFrom.xlsm", True, True)
Set dest = Workbooks.Open("C:\Users\RP\Desktop\CopyTo.xlsm", True, True)

Dim startRow As Integer, endRow as Integer
With src.Sheets("Sheet1")
    startRow = .Range("B:B").Find(what:="*", after:=.Range("B1")).Row
    endRow = .Range("B:B").Find(what:="", after:=.Range("B1"),      searchdirection:=xlPrevious).Row
End With

dest.Worksheets("PasteHere").Range("B1").Value = _ 
    src.Worksheets("Sheet1").Range("B1").Value

You can learn more about the Range object here:

https://learn.microsoft.com/en-us/office/vba/api/excel.range(object)