Select Previously Selected worksheet (and/or select previouslyselected workbook)

[Dave K]

Is anyone aware of VBA code that would allow me to auto-select the
previously active worksheet (within a particular workbook....or within
another open workbook)?

In essence, I am trying to build a routine that will allow me to
automatically do a vlookup between two data lists in two sheets with a
single step, rather than go through the process of specifying the
particular columns

Thanks for any suggestions.

FYI, i am using Excel 2003.

 

[Gord Dibben]

Quite difficult to use VLOOKUP without specifying a lookup value and lookup
table.

Can you explain what you mean by "auto-select"?

Normally one would type =VLOOKUP(value, then switch to source sheet,
select the table, type any arguments, add a closing parens then hit ENTER key.

What part of this needs automating?

Can you explain what you mean by "automatically do a vlookup between two data
lists in two sheets with a single step"?


Gord Dibben MS Excel MVP

 

[Clif McIrvin]

Is anyone aware of VBA code that would allow me to auto-select the
previously active worksheet (within a particular workbook....or within
another open workbook)?

In essence, I am trying to build a routine that will allow me to
automatically do a vlookup between two data lists in two sheets with a
single step, rather than go through the process of specifying the
particular columns

Thanks for any suggestions.

FYI, i am using Excel 2003.

No --- at least, I'm not <grin>

What comes to mind is the built-in workbook events. Since you're
already writing VBA, you could possibly create some public variables
(class properties?) that are visible to any open workbook, and include
code in the [ ThisWorkbook ] object for each of the workbooks of
interest to remember the current and previous active sheet. I'd look at
the Workbook_Activate and Workbook_SheetActivate events for starters.

 

[Dave K]

Thanks Gord. I do so many of these every day that it will save me some
time to create a vlookup shortcut. Additionally, i am looking at ways
of simplifying the process for other end users.

My thoughts were to manually place the associated vlookup columns in A
and B of each worksheet, and then to use a shortcut combination to
"automate" the use of the function, including automatically filling
the entire column with the formula.

 

[Gord Dibben]

Here's a vlookup formula you could use to collect data from book2sheet2 columns
A and B to B1 of book1sheet1

Assuming both workbooks are open.

=VLOOKUP(A1,[Book2.xls]Sheet2'!$A$1:$B$1000,FALSE)

In your automating scenario what would be the variables?

Lookup value cell....................assume relative reference in same column?

Table range............same A:B in different books and different sheets?

Variables in that case would be book name and sheet name which would have to be
determined in some manner................input by user or?

Column index number..............2 in every table?

Fill down to where? Surely not the entire column.

I'm thinking of maybe INDIRECT function and range names

=VLOOKUP(A1,INDIRECT("F1"),2,FALSE) where F1 holds a range name input by user.


Gord

 

[Gord Dibben]

Please note correction..............drop the double quotes.

=VLOOKUP(A1,INDIRECT("F1"),2,FALSE) should be

=VLOOKUP(A1,INDIRECT(F1),2,FALSE)


Gord

Here's a vlookup formula you could use to collect data from book2sheet2 columns
A and B to B1 of book1sheet1

Assuming both workbooks are open.

=VLOOKUP(A1,[Book2.xls]Sheet2'!$A$1:$B$1000,FALSE)

In your automating scenario what would be the variables?

Lookup value cell....................assume relative reference in same column?

Table range............same A:B in different books and different sheets?

Variables in that case would be book name and sheet name which would have to be
determined in some manner................input by user or?

Column index number..............2 in every table?

Fill down to where? Surely not the entire column.

I'm thinking of maybe INDIRECT function and range names

=VLOOKUP(A1,INDIRECT("F1"),2,FALSE) where F1 holds a range name input by user.


Gord

Thanks Gord. I do so many of these every day that it will save me some
time to create a vlookup shortcut. Additionally, i am looking at ways
of simplifying the process for other end users.

My thoughts were to manually place the associated vlookup columns in A
and B of each worksheet, and then to use a shortcut combination to
"automate" the use of the function, including automatically filling
the entire column with the formula.