VBA code to paste list into two different workbooks

[Anthropologista]

Hello,
I have several macros that I use regularly, but rarely have to modify. Below I have the original macro that copies a list from "Master" workbook and pastes it into "Translator" workbook. Below that is the edited version, which simply pastes the list into a second translator (one for Paypal and one for Venmo). Can anyone identify the error in my code? I cannot understand why the original macro works perfectly yet the edited version does not.

Thanks for any tips!
Sarah

ORIGINAL:

Sub UpdateDonorsSupporters()
Dim wbSource As Workbook 'Master
Dim wbTarget As Workbook 'Translator
Dim DateCell As Range
Dim DateCell2 As Range

Workbooks.Open ("https://treeoflifeusa-my.sharepoint.com/personal/tol_treeoflifeusa_onmicrosoft_com/Documents/Private/Accounting/Paypal%20Translator.xlsm")

Set wbSource = Workbooks("TOL USA Master.xlsm")
Set wbTarget = Workbooks("Paypal Translator.xlsm")


'1. Copies Donors & Supporters list, pastes into Translator, and updates date in Translator
wbSource.Sheets("Donors & Supporters").Range("A3:C1000").Copy
wbTarget.Sheets("Donor Mstr List").Select
Range("A3").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DateCell = wbTarget.Sheets("Donor Mstr List").Range("D1")
DateCell.NumberFormat = "dd-mmm-yy"
DateCell = Date

'2. Updates Date in Master and exits CutCopyMode
wbSource.Activate
Sheets("Donors & Supporters").Activate
Application.CutCopyMode = False
Set DateCell2 = wbSource.Sheets("Donors & Supporters").Range("D1")
DateCell2.NumberFormat = "dd-mmm-yy"
DateCell2 = Date

'3. Asks user if they want to save and close the Translator
Dim Answer As Integer
Answer = MsgBox("Would you like to save and close the Translator?", vbYesNo)
If Answer = vbYes Then
Workbooks("Paypal Translator.xlsm").Close SaveChanges:=True

Else
End If
End Sub

EDITED:

Sub UpdateDonorsSupporters()
Dim wbSource As Workbook 'Master
Dim wbTarget As Workbook 'Paypal Translator
Dim wbTarget2 As Workbook 'Venmo Translator
Dim DateCell As Range 'Paypal date
Dim DateCell2 As Range 'Venmo date
Dim DateCell3 As Range 'Master date


Workbooks.Open ("https://treeoflifeusa-my.sharepoint.com/personal/tol_treeoflifeusa_onmicrosoft_com/Documents/Private/Accounting/Paypal%20Translator.xlsm")
Workbooks.Open ("https://treeoflifeusa-my.sharepoint.com/personal/tol_treeoflifeusa_onmicrosoft_com/Documents/Private/Accounting/Venmo%20Translator.xlsm")


Set wbSource = Workbooks("TOL USA Master.xlsm")
Set wbTarget = Workbooks("Paypal Translator.xlsm")
Set wbTarget2 = Workbooks("Venmo Translator.xlsm")

'1. Copies Donors & Supporters list, pastes into PAYPAL Translator, and updates date in Paypal Translator
wbSource.Sheets("Donors & Supporters").Range("A3:C1000").Copy
wbTarget.Sheets("Donor Mstr List").Select
Range("A3").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DateCell = wbTarget.Sheets("Donor Mstr List").Range("D1")
DateCell.NumberFormat = "dd-mmm-yy"
DateCell = Date

'2. Pastes D&S list into VENMO Translator and updates date in Venmo Translator
wbTarget2.Sheets("Donor Mstr List").Select
Range("A3").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DateCell2 = wbTarget2.Sheets("Donor Mstr List").Range("D1")
DateCell2.NumberFormat = "dd-mmm-yy"
DateCell2 = Date

'3. Exits CutCopyMode and updates Date in Master
wbSource.Activate
Sheets("Donors & Supporters").Activate
Application.CutCopyMode = False
Set DateCell3 = wbSource.Sheets("Donors & Supporters").Range("D1")
DateCell3.NumberFormat = "dd-mmm-yy"
DateCell3 = Date

'4. Asks user if they want to save and close the Translators
Dim Answer As Integer
Answer = MsgBox("Would you like to save and close the Translators?", vbYesNo)
If Answer = vbYes Then
Workbooks("Paypal Translator.xlsm").Close SaveChanges:=True
Workbooks("Venmo Translator.xlsm").Close SaveChanges:=True
Else
End If
End Sub

 

[NdNoviceHlp]

A couple of things. Your set statements don't include the full file paths (Both source and target wbs). Trial...

Set wbTarget = Workbooks.Open("https://treeoflifeusa-my.sharepoin....etc")

Using selection and activate are rarely needed and slow things down. Here's an example...

wbTarget.Sheets("Donor Mstr List").Range("A3").PasteSpecial Paste:=xlPasteValues, _
           Operation:=xlNone, SkipBlanks:=False, Transpose:=False

HTH. Dave

 

[Anthropologista]

wbTarget.Sheets("Donor Mstr List").Range("A3").PasteSpecial Paste:=xlPasteValues,

Thank you, Dave!! Your suggestions simplified my code and (with the addition of another copy function) the macro works perfectly now. I really appreciate your help.
Sarah

 

[NdNoviceHlp]

You are welcome Sarah Thanks for posting your outcome. Be safe. Dave