Copy to another workbook - prompt for input

[JenL]

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

 

[Rob Wills]

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

 

[OssieMac]

Hi,

Just a little more info will help.
Do you want the macro to create the new workbook or does the workbook
already exist? If already exists, do you want the user to Browse for it?

If you want the macro to create it, in what format do you want the date
appended to the new workbook name? For example. NewBook yyyy-mm-dd or NewBook
mm-dd-yyyy. I like the first format because they sort well.

What about the folder/directory of the new workbook? Will it in the same
location as the one that contains the macro or maybe a sub folder of the
current folder.

 

[JenL]

Unfortuneatly, it will always be in a different folder. And I do not have
the choice of the name of the file. It is named with mm_dd_yy as the end of
the file name.

I have the below macro working so far. However, I now need it to start on a
different row each time based on user input.

So this time the copy range would be C71309
But next week it might be C78309

The ending cell and columns will always be the same. The first row is what
I need to prompt for input.

Any suggestions on how to make this better?

Sub CopyCashFlow()
'
' Macro to copy the data input columns from the country files
' to the consolidated LAO cash flow file.
' Macro recorded 2/6/2009 by jlefief
'

Dim strFilename

strFilename = InputBox("Enter the name of the destination file.
Example: Forecast template - LAO_02_06_09.xls", "Enter the name of the
destination file")


Windows("LAO Cash Flow Input Template-ARG.xls").Activate
Sheets("Argentina ARS").Activate
Range("C71309").Select
Selection.Copy
Windows(strFilename).Activate
Sheets("Argentina ARS").Activate
Range("C71").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=1

End Sub