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.

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.

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.

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.