VBA code to paste list into two different workbooks

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
12
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:
Code:
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:
Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
A couple of things. Your set statements don't include the full file paths (Both source and target wbs). Trial...
Code:
Set wbTarget = Workbooks.Open("https://treeoflifeusa-my.sharepoin....etc")
Using selection and activate are rarely needed and slow things down. Here's an example...
Code:
wbTarget.Sheets("Donor Mstr List").Range("A3").PasteSpecial Paste:=xlPasteValues, _
           Operation:=xlNone, SkipBlanks:=False, Transpose:=False
HTH. Dave
 
Solution

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,060
Messages
5,639,828
Members
417,117
Latest member
Ravi Pandey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top