Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
Refering to the current workbook in a macro | Excel Programming | |||
how can i get a macro in personal.xls to know my current workbook? | Excel Programming | |||
Macro to insert Current Time into protected cell | Excel Programming | |||
Help with Macro or VBA script - Insert current Time/Date for different records | Excel Programming |