Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sheets Skip Macro
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 |
#2
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "NICK" wrote in message ... 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 |
#3
|
|||
|
|||
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) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date macro | Excel Discussion (Misc queries) | |||
Can't get simple macro to run | Excel Worksheet Functions | |||
Macro for moving sheets | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro for multiple charts | Excel Worksheet Functions |