Macro Using VLookups
 

I am not sure if I am getting way ahead of myself for someone who just
started learning Excel VBA last week. But I have been trying to write a
Macro but have been very unsucessful with writing my most recent Macro.

Basically the Macro is to help me open up an external file and populate
information from the external file using a VLookup. This part works no
prob. But next week I have to write another Macro to do the same task as
I am not sure how to make my Macro Vlookup be generic so it can do the
lookup form only the file I specified.

Sub ProductionImport()
'
' ProductionImport Macro
' Macro recorded 10/14/2003 by Oshiz
'

'
myFile = Application.GetOpenFilename("Excel Files, *.xls")
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-28], '[Carlo Production
10-11-03.xls]Summary'!R12C1:R36C26, 3, FALSE)"
Range("AD11").Select
Selection.AutoFill Destination:=Range("AD11:AD29"),
Type:=xlFillDefault
Range("AD11:AD29").Select
End Sub

Next week I Will have a diff Production File. How do I get my macro to
bypass being stuck on pulling data from the 10-11-03 Production File?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Macro Using VLookups
 

Sub ProductionImport()
'
' ProductionImport Macro
' Macro recorded 10/14/2003 by Oshiz
'

'
Dim sName as String
Dim sh as Worksheet
Dim myFile as String
set sh = Activesheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
workbooks.Open myfile
sName = Activeworkbook.Name
sh.parent.Activate
sh.Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-28], '[" & sName & _
"]Summary'!R12C1:R36C26, 3, FALSE)"
Range("AD11").Select
Selection.AutoFill Destination:=Range("AD11:AD29"), _
Type:=xlFillDefault
Range("AD11:AD29").Select
End Sub


-- Regards,
Tom Ogilvy

"Oli Oshiz" wrote in message
...

I am not sure if I am getting way ahead of myself for someone who just
started learning Excel VBA last week. But I have been trying to write a
Macro but have been very unsucessful with writing my most recent Macro.

Basically the Macro is to help me open up an external file and populate
information from the external file using a VLookup. This part works no
prob. But next week I have to write another Macro to do the same task as
I am not sure how to make my Macro Vlookup be generic so it can do the
lookup form only the file I specified.

Sub ProductionImport()
'
' ProductionImport Macro
' Macro recorded 10/14/2003 by Oshiz
'

'
myFile = Application.GetOpenFilename("Excel Files, *.xls")
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-28], '[Carlo Production
10-11-03.xls]Summary'!R12C1:R36C26, 3, FALSE)"
Range("AD11").Select
Selection.AutoFill Destination:=Range("AD11:AD29"),
Type:=xlFillDefault
Range("AD11:AD29").Select
End Sub

Next week I Will have a diff Production File. How do I get my macro to
bypass being stuck on pulling data from the 10-11-03 Production File?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!