Re: Prompt for filename in excel macro VBA

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

filename = InputBox("Enter file name")

HTH
--
AP
<shamble@xxxxxxxxx> a écrit dans le message de
news:1142259252.103256.316670@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Folks,

I'm setting up a basic macro to copy data from one spread*** to the
other, the only issue I have is the *** that the data is being copied
to will have a name that changes on a weekly basis. Is there anyway to
prompt to enter the filename? or any other way around this. My code is
attached below, its the "Windows("060313_hc.xls").Activate" where the
filename will change. Thanks in adavance


Sub UpdateCurrentWeek()
'
' UpdateCurrentWeek Macro
' Updates current week data with information from Mi Central
'

'
Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowerhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
Active***.Paste
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leeds ASC").Select
Range("D7").Select
ActiveWindow.SmallScroll Down:=57
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Sheets("Leeds ASC").Select
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
Active***.Paste
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
Sheets("Leicester").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Leicester").Select
ActiveWindow.SmallScroll Down:=-6
Range("D7:T7").Select
ActiveWindow.SmallScroll Down:=84
Range("D7:T95").Select
ActiveWindow.SmallScroll Down:=-21
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
Active***.Paste
Range("A1").Select
Sheets("Oldbury").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Oldbury").Select
ActiveWindow.SmallScroll Down:=-9
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=81
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-12
Range("D7").Select
Active***.Paste
Range("A1").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Stockport").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=66
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Sheets("Stockport").Select
Range("D7").Select
Active***.Paste
Sheets("Uddingston").Select
Windows("AManpowerhcv0.2.xls").Activate
Sheets("Uddingston").Select
Range("D7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=72
Range("D7:T95").Select
Application.CutCopyMode = False
Selection.Copy
Windows("060313_hc.xls").Activate
Range("D7").Select
Active***.Paste
Windows("AManpowerhcv0.2.xls").Activate
ActiveWindow.Close
Range("A1").Select
Sheets("ASC").Select
Range("A1").Select
End Sub



.

Quantcast