copy and paste special values between two workbooks

[maacmaac]

I am working with two workbooks. I am trying to copy information from workbook "wkbSrc" and paste special as values into workbook "wkbDest".

I want to copy all the cells with values from column A starting with row 2 from "wkbSrc" and paste as values in column A of "wkbDest" but starting with next empty row of "wkbDest".

As example in "wkbSrc" I have values in A2:A12. In "wbkDest" my last row of data is A81. In this example, copy A2:A12 and paste values to A82:A92.

The code works fine if I leave out the ".PasteSpecial xlPasteValues" part of the code. It is when I add this part that it won't work. Thanks in advance for any comments.

Sub CopyPaste()

Dim wkbDest As Workbook
Dim wkbSrc As Workbook
Dim LastRowDest As Long
Dim LastRowSrc As Long


    Set wkbDest = ThisWorkbook
    Set wkbDest = ActiveWorkbook
    Set wkbSrc = Workbooks.Open("E:\Projects\ProjectLetterPrint\O Drive\Low Priced Security Correspondence Master File.xlsx")
    LastRowSrc = Range("A" & Rows.Count).End(xlUp).Row
    
    With wsDest
        LastRowDest = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With

    wkbSrc.Worksheets("Source").Range("A2:A" & LastRowSrc).Copy Destination:=wkbDest.Worksheets("Dest").Range("A" & LastRowDest).PasteSpecial xlPasteValues
    
    wkbSrc.Close SaveChanges:=False
  
End Sub

[jraj1106]

Hi

Try this:

Sub CopyPaste()
    Dim wkbDest As Workbook
    Dim wkbSrc As Workbook
    Dim LastRowDest As Long
    Dim LastRowSrc As Long


    Set wkbDest = ThisWorkbook
    Set wkbDest = ActiveWorkbook
    Set wkbSrc = Workbooks.Open("E:\Projects\ProjectLetterPrint\O Drive\Low Priced Security Correspondence Master File.xlsx")
    LastRowSrc = Range("A" & Rows.Count).End(xlUp).Row
    
    With wsDest
        LastRowDest = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With

    'wkbSrc.Worksheets("Source").Range("A2:A" & LastRowSrc).Copy Destination:=wkbDest.Worksheets("Dest").Range("A" & LastRowDest).PasteSpecial xlPasteValues
    wkbSrc.Worksheets("Source").Range("A2:A" & LastRowSrc).Copy (wkbDest.Worksheets("Dest").Range("A" & LastRowDest))
    'OR YOU CAN TRY THIS TOO
'    wkbDest.Worksheets("Dest").Select
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    wkbSrc.Close SaveChanges:=False
End Sub