Re: Prompt for filename in excel macro VBA

Sorry guys, I'm a bit thick when it comes to things like this, what
code would i insert into the part with <<<<<< next to it as i'm
assuming this is the part i need to change, thanks:

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




Bob Phillips wrote:
I would use the GetOpenFilename method, See VBA help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <ardus.petus@xxxxxxxxxxx> wrote in message
news:OIf%23UoqRGHA.1576@xxxxxxxxxxxxxxxxxxxxxxx
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




.