Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to lookup value and open tab based on lookup value
I'm trying to figure out if its possible to write a macro that will take the
value in column 9 of a given row (row will change) and lookup or match it in a table in a different worksheet named ValidationLists D2:E13, then based on the information obtained in the lookup open the corresponding tab which is named the same and located in the same workbook. I'll then have it go on to make a copy of that tab and fill in additional information from the original row in the Input tab where it originally started. I've worked only a little with macros and am just trying to learn a little of VBA to enhance what I can do with them. Thanks for any suggestions or direction. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to lookup value and open tab based on lookup value
brandyvine,
If the 'given row' is the active cell's row, this works. Change ActiveCell.Row to any other valid row reference.... Worksheets(Application.VLookup(Cells(ActiveCell.Ro w, 9).Value, _ Worksheets("ValidationLists").Range("D2:E13"), 2, False)).Activate Also, you don't need to activate a sheet to work on it.... HTH, Bernie MS Excel MVP "brandyvine" wrote in message ... I'm trying to figure out if its possible to write a macro that will take the value in column 9 of a given row (row will change) and lookup or match it in a table in a different worksheet named ValidationLists D2:E13, then based on the information obtained in the lookup open the corresponding tab which is named the same and located in the same workbook. I'll then have it go on to make a copy of that tab and fill in additional information from the original row in the Input tab where it originally started. I've worked only a little with macros and am just trying to learn a little of VBA to enhance what I can do with them. Thanks for any suggestions or direction. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to lookup value and open tab based on lookup value
That worked beautifully! Thank you very much!! brandyvine
"Bernie Deitrick" wrote: brandyvine, If the 'given row' is the active cell's row, this works. Change ActiveCell.Row to any other valid row reference.... Worksheets(Application.VLookup(Cells(ActiveCell.Ro w, 9).Value, _ Worksheets("ValidationLists").Range("D2:E13"), 2, False)).Activate Also, you don't need to activate a sheet to work on it.... HTH, Bernie MS Excel MVP "brandyvine" wrote in message ... I'm trying to figure out if its possible to write a macro that will take the value in column 9 of a given row (row will change) and lookup or match it in a table in a different worksheet named ValidationLists D2:E13, then based on the information obtained in the lookup open the corresponding tab which is named the same and located in the same workbook. I'll then have it go on to make a copy of that tab and fill in additional information from the original row in the Input tab where it originally started. I've worked only a little with macros and am just trying to learn a little of VBA to enhance what I can do with them. Thanks for any suggestions or direction. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to lookup value and open tab based on lookup value
Brandyvine,
i hope you can help, this is the closest to my query i could find..... What i am trying to do is a simple macro that will go and retrieve, this i have no problem with, what i need to do is write a line that tells it to go to the worksheet based on a value in cell A1 on a different sheet, so: i have a work sheet named summary and then sheets for each month Jan08, Feb08 etc each containing data dumps with over 50000 entries, my macro will go to a desired worksheet, filter it and return a selection of rows based on my criteria, i need help with directing to the worksheet called '=A1' so if in cell A1 i enter Jan08 it goes to Jan08 tab and carries out my filter.... etc Please Can you help???? Kind Regards Nick "Bernie Deitrick" wrote: brandyvine, If the 'given row' is the active cell's row, this works. Change ActiveCell.Row to any other valid row reference.... Worksheets(Application.VLookup(Cells(ActiveCell.Ro w, 9).Value, _ Worksheets("ValidationLists").Range("D2:E13"), 2, False)).Activate Also, you don't need to activate a sheet to work on it.... HTH, Bernie MS Excel MVP "brandyvine" wrote in message ... I'm trying to figure out if its possible to write a macro that will take the value in column 9 of a given row (row will change) and lookup or match it in a table in a different worksheet named ValidationLists D2:E13, then based on the information obtained in the lookup open the corresponding tab which is named the same and located in the same workbook. I'll then have it go on to make a copy of that tab and fill in additional information from the original row in the Input tab where it originally started. I've worked only a little with macros and am just trying to learn a little of VBA to enhance what I can do with them. Thanks for any suggestions or direction. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
Lookup Based on 4 values | Excel Discussion (Misc queries) | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Lookup based on two criteria in 1 row | Excel Discussion (Misc queries) | |||
Row lookup based on a given value in the same row | Excel Programming |