PasteSpecial Range of Cells from one Workbook to Another in VBA

[Anthropologista]

Noob alert. I’ve been teaching myself VBA for approx. one month, so my code may conjure up images of a teenager's messy bedroom. Please excuse any and all rookie mistakes.

I’m attempting to copy a list of data (#1) and paste values into two destinations, one in another sheet within the source workbook (#2), and the other in a different workbook (#3). I cannot get event #3 to work. I can access the destination workbook, but it fails on the paste and gives me “error 1004: PasteSpecial method of range class failed”.

Any help is appreciated. Thanks.

Sub UpdateAllTOLCat()
Dim wbSource As Workbook 'Master
Dim wbTarget As Workbook 'Translator
 
Set wbSource = Workbooks("TOL USA Master.xlsm")
Set wbTarget = Workbooks("Paypal Translator v5a.xlsm")
 
'1. Finds all TOL categories and copies them
    Dim AR  As Long
    wbSource.Sheets("TOL Categories").Activate
    AR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A3:A" & AR).Select
    Selection.Copy
 
'2. Finds cell with second "Donation Category" in Ledger and pastes list one row below.  Also reruns filter.
    Sheets("2017").Activate
       Range("H2:H1450").Find("Donation Category").Offset(1, 0).PasteSpecial xlPasteValues
    ActiveSheet.Range("$A$2:$P$1450").AutoFilter Field:=8, Criteria1:="<>"
   
'3. Activates Translator and pastes category list
    Windows("Paypal Translator v5a.xlsm").Activate
    Sheets("TOL Categories").Select
    Range("A2").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TOL USA Master.xlsm").Activate
    Application.CutCopyMode = False
 
'4. Asks user if they want to update the date
    Dim Answer As Integer
    Dim DateCell As Range
       
    Set DateCell = ActiveWorkbook.Sheets("TOL Categories").Range("A2")
    DateCell.NumberFormat = "dd-mmm-yy"
   
    Answer = MsgBox("Would you like to update the date?", vbYesNo)
    If Answer = vbYes Then
    DateCell = Date
       
    Else
    End If
End Sub

 

[JLGWhiz]

'3. Activates Translator and pastes category list
Windows("Paypal Translator v5a.xlsm").Activate
Sheets("TOL Categories").Select
Range("A2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The VBA copy does not necessarily copy to the clipboard, so you would have to execute another copy for the above paste action to be effective. As written it would error because there is nothing to paste, as opposed to Excel where you copy to the clipboard and then make multiple paste actions from the clipboard.

 

[Anthropologista]

That solves it! Thank you for the quick and accurate response, JLGWhiz. I didn't know that VBA does not use the clipboard for copy actions. It's interesting since I still need to exit CutCopyPaste mode in my code... Many thanks!

 

[JLGWhiz]

That solves it! Thank you for the quick and accurate response, JLGWhiz. I didn't know that VBA does not use the clipboard for copy actions. It's interesting since I still need to exit CutCopyPaste mode in my code... Many thanks!

I am not a techie, so my understanding of the cut/copy action is limited. But I believe it does hold the initial copy in memory and can be accessed on a limited basis, such as when you pastespecial values, then paste formats. But it seems to lose the copy data if you change the paste range. I used to be able to pull up some articles on the web that explained some of the VBA software functions, but apparently they have been taken down in recent years. Maybe Microsoft exerted some legal pressure, since it was giving free lessons on stuff that they charge fees for.
regards, JLG