Sheets Skip Macro

[Guest]

Hello

I have a lot of sheets in an excel workbook and a front summary sheet where
i have a vlookup formula that i would like to copy across to the right
however each column needs to change the formula to pick up the data in the
different sheets. Does anyone know a macro that will help me so that each
column copied across looks up in the next sheet.

Eg. 5 sheets - Daniel, Mark, Byran, Hugh & Paul
on the summary page i need it to go =VLOOKUP(ref,Daniel!F2:F17,col,false)
then =VLOOKUP(ref,Mark!F2:F17,col,false) then
=VLOOKUP(ref,Byran!F2:F17,col,false) then =VLOOKUP(ref,Hugh!F2:F17,col,false)
and then =VLOOKUP(ref,Paul!F2:F17,col,false).

Because I have >50 sheets doing a find and replace is quite time consuming

Cheers
Nick

 

[Max]

One way

Put the reference range in A1: F2:G17 (say)

List the names of the sheets across in B1, C1, D1 ... etc
e.g. in B1: Daniel, in C1: Mark , tec

Then you could put this formula in B2:

=VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&$A$1),2,FALSE)

and simply copy B2 across

B2 will return the same as:
=VLOOKUP($A2,Daniel!F2:G17,2,FALSE)

and C2 the same as:
=VLOOKUP($A2,Mark!F2:G17,2,FALSE)

and so on

 

[Max]

Because I have >50 sheets ..

Some add-ons ..

1. Try the sub below to list all the sheetnames in the book at one go:

Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the whitespace on the right

-------begin vba-----
Sub SheetNames()
'Peo Sjoblom in .worksheet.functions Jul '02
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
-------endvba------

Press Alt+Q to get back to Excel

In a *new* sheet, say Sheet1, press Alt+F8
Select "SheetNames" > Run

The sheetnames will be listed in A1 down, in this sequence:

1st sheet (leftmost) will be listed in A1,
2nd sheet in A2, and so on

Hidden sheets will also be listed
and will appear after the last (rightmost) sheet

--

2. So, assuming you now have in Sheet1, in A1 downwards,
a tidied up list of all the 50 + sheetnames:

Daniel
Mark
Byran
Hugh
Paul
etc

In the sheet: Summary
[ which contains the earlier VLOOKUP(.. INDIRECT(...) ..) formulas
suggested ]

just put in B1: =OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,)
and copy B1 across as many cols as you have names to be listed
(this saves you having to manually list all the 50+ sheetnames in B1, C1,
etc)