Insert macro using current workbook
 

I have a macro that I'd like to use in a workbook whose name changes each
month. It inserts a column in the left most column and opens a specific file
to do a vlookup. In this example, my code is in the file
Windows("june09apdist.xls") but I'd like for it to be in whatever file that
I'm currently working in regardless of the name. Thanks in advance!

ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
Workbooks.Open FileName:= _
"S:\DFS-Jaffa-WTFiles\HBI Department Naming Convention\HBI
Department Names 01062009.xls"
Windows("jun09apdist.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[9],'[HBI Department Names 01062009.xls]FY09
Dates'!R1C1:R368C2,2,0)"
End Sub

Insert macro using current workbook
 

'Reference the workbook object

Dim wb As Workbook
Set wb = ActiveWorkbook

'I havent changed the code from here

ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
Workbooks.Open Filename:= _
"S:\DFS-Jaffa-WTFiles\HBI Department Naming Convention\HBI " & _
"Department Names 01062009.xls"

'change
wb.Activate

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[9],'[HBI Department Names 01062009.xls]FY09 "
Dates '!R1C1:R368C2,2,0)"


If this post helps click Yes
---------------
Jacob Skaria


"Metro" wrote:

I have a macro that I'd like to use in a workbook whose name changes each
month. It inserts a column in the left most column and opens a specific file
to do a vlookup. In this example, my code is in the file
Windows("june09apdist.xls") but I'd like for it to be in whatever file that
I'm currently working in regardless of the name. Thanks in advance!

ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
Workbooks.Open FileName:= _
"S:\DFS-Jaffa-WTFiles\HBI Department Naming Convention\HBI
Department Names 01062009.xls"
Windows("jun09apdist.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[9],'[HBI Department Names 01062009.xls]FY09
Dates'!R1C1:R368C2,2,0)"
End Sub