Hi,

Firstly - (almost) any use of activate and select in programming is
unnecessary

try this:

Sub CopyTargetSheet()
dim dteUser as Date

dteUser = inputbox("Please enter the required date","USER INPUT
REQUIRED",format(now(),"DD MMM YYYY"))

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



HTH

"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