Copy to another workbook - prompt for input
 

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