I'd start the macro on the sheet that gets the values.

Option Explicit
Sub CopyTargetSheet()

Dim RngToCopy as range
Dim DestCell as range

with workbooks("BRAZIL Data Upload.xls").worksheets("BRAZIL Actuals")
set rngtocopy = .range("D4:o9")
end with

with activesheet
set destcell = .range("d4")
end with

rngtocopy.copy
destcell.pasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

Untested, uncompiled. Watch for typos.

And that "Sending" workbook still has to be opened.

JenL wrote:

Hello,

I have the below macro which copies several ranges from one workbook to
another that works great. I want to duplicate this for another purpose.
However my destination workbook that will have the data pasted into it is
named something different every week. The columns/cells are always in the
same place, but the date is put on the end of the file name each week. So
how do I modify this macro so it will prompt me and ask which file to paste
into when it runs?

THANKS!

Sub CopyTargetSheet()

Windows("BRAZIL Data Upload.xls").Activate
Sheets("BRAZIL Actuals").Activate
Range("D4:O9").Select
Selection.Copy
Windows("2008 Target Sheet.xls").Activate
Sheets("BRAZIL Actuals").Activate
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

--

Dave Peterson