Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumary of 150 sheets info in one sheet
People,
I have a workbook with 150 sheets, being each one a form of a little research i've made with some clients. Now, i want to put all that information in a single sheet, making a line per sheet and a col. per info i want. By the way, all the 150 sheets are equal and obey a singular name (like, FORM (1), FORM (2), FORM (3), ..., FORM (150)). For example, my sheets have three relevant informations: my client's name, his phone number and his e-mail. so, i've put in my "Results Sheet" 4 columns: 1 for the sequential number (1, 2, 3, 4, 5, etc.), 1 for the name, 1 for the phone and 1 for the e-mail. Then, i've linked the first line with the apropriate cell on the FORM (1) sheet. Beautiful. But now, i want to do the same thing to the other 149 sheets and, of course, don't wanna do it manually. Could u people give a little world of advice, plz?? Thx a lot! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumary of 150 sheets info in one sheet
This macro will do it, altho you may need to adjust the cell
references. The macro will start writing formulas starting at whatever cell is highlighted when you run this. Sub All_Tabs() Dim K As Byte For K = 1 To 150 'column A ActiveCell.Offset(K - 1, 0).Formula = "='Form (" & K & ")'!a1" 'column B ActiveCell.Offset(K - 1, 1).Formula = "='Form (" & K & ")'!b1" 'column C ActiveCell.Offset(K - 1, 2).Formula = "='Form (" & K & ")'!c1" 'column D ActiveCell.Offset(K - 1, 3).Formula = "='Form (" & K & ")'!d1" 'column E ActiveCell.Offset(K - 1, 4).Formula = "='Form (" & K & ")'!e1" Next K End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumary of 150 sheets info in one sheet
Ty a lot man!!! God bless u for that! "Dave O" escreveu: This macro will do it, altho you may need to adjust the cell references. The macro will start writing formulas starting at whatever cell is highlighted when you run this. Sub All_Tabs() Dim K As Byte For K = 1 To 150 'column A ActiveCell.Offset(K - 1, 0).Formula = "='Form (" & K & ")'!a1" 'column B ActiveCell.Offset(K - 1, 1).Formula = "='Form (" & K & ")'!b1" 'column C ActiveCell.Offset(K - 1, 2).Formula = "='Form (" & K & ")'!c1" 'column D ActiveCell.Offset(K - 1, 3).Formula = "='Form (" & K & ")'!d1" 'column E ActiveCell.Offset(K - 1, 4).Formula = "='Form (" & K & ")'!e1" Next K End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumary of 150 sheets info in one sheet
Glad to help!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data exctraction from multiple sheets | Excel Discussion (Misc queries) | |||
Summary Sheet help with multiple sheets | Excel Discussion (Misc queries) | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
Selecting Last Sheet | Excel Worksheet Functions | |||
Compare 2 Sheets and Extract Unique Info to a 3rd Sheet | Excel Discussion (Misc queries) |