# Thread: Listing Data from Multiple Sheets (XP)

1. ## Listing Data from Multiple Sheets (XP)

Help!
I have a 28 page workbook and I want to create a list on another page of all the values in Cell B4 of each sheet. Sounds really simple, but I've just given up solving this for myself!
On a similar vein - how can I list all the sheet names within a workbook?
Thanks,

2. ## Re: Listing Data from Multiple Sheets (XP)

Hi David,

To list all sheet names in Column A of Sheet 1, try a macro like:

Sub GetSheetNames()
For i = 1 To Sheets.Count
Sheets(1).Range("A1").Offset(i - 1, 0).Value = Sheets(i).Name
Next i
End Sub

Cheers

3. ## Re: Listing Data from Multiple Sheets (XP)

To list the values of B4 in the first 28 worksheets in the 29th sheet:

Sub ListB4()
Dim i As Long
For i = 1 To 28
' Worksheet name in column A
Worksheets(29).Range("A" & i) = Worksheets(i).Name
' Value of B4 in column B
Worksheets(29).Range("B" & i) = Worksheets(i).Range("B4")
Next i
End Sub

4. ## Re: Listing Data from Multiple Sheets (XP)

Macropod: That macro will put the list in the first sheet in the workbook, not on a sheet named "Sheet 1"

5. ## Re: Listing Data from Multiple Sheets (XP)

If the sheets are named Sheet1 to Sheet28, then you could put the following formula into cell A1 of the sheet where you want the values from B4 and copy it down to cell A28:

<pre>=INDIRECT("Sheet"&ROW()&"!A1")
</pre>

If the sheets have other names, then the formula would have to be different, and will probably require a list of the sheet names.
Getting a list of sheet names will require a macro like Macropods or Hans. The exact form of the macro would depend on where you want the list. Also, if you want the list automatically update, the macro would need to be put into one of the event routines.

6. ## Re: Listing Data from Multiple Sheets (XP)

Thanks Everyone,
I've combined all your suggestions and I think I can now do what I want.
I still reckon this should be easier for us non-programmers, and I still don't understand WHO microsoft actually targets its software at. Maybe big corporations with IT departments to spend time programming?
So thanks for all your help.

7. ## Re: Listing Data from Multiple Sheets (XP)

You can do lots of things in Excel without programming. People have built large and complicated models using worksheet formulas only. But some things are more easily done using VBA.

8. ## Re: Listing Data from Multiple Sheets (XP)

Link corrected by HansV - there were three slashes after http: instead of two.

Hi David

To list your sheets you could use ASAP-Utilities at http://www.asap-utilities.com this will allow you to create and index page with all sheets clickable.

Regards

9. ## Re: Listing Data from Multiple Sheets (XP)

They target it at people who are smart enough to find Woody's Lounge. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

Touche!

11. ## Re: Listing Data from Multiple Sheets (XP)

Sounds like I better take a look at ASAP....

How'd you know I'm over forty?? %-)

12. ## Re: Listing Data from Multiple Sheets (XP)

OK then, I take that as an invitation to ask more stupid questions! ;-)
I'm trying to turn Han's cell listing macro around so it lists ACROSS the page, instead of down - so I've tried to use the R1C1 cell reference instead of A1, but I can't find how Excel wants it spelt out !!
Please kind gurus, can someone write me another example macro.
Heaps thanks. %-)

13. ## Re: Listing Data from Multiple Sheets (XP)

Sub ListB4()
Dim i As Long
For i = 1 To 28
' Worksheet name in row 1
Worksheets(29).Cells(1, i) = Worksheets(i).Name
' Value of B4 in row 2
Worksheets(29).Cells(2, i) = Worksheets(i).Range("B4")
Next i
End Sub

14. ## Re: Listing Data from Multiple Sheets (XP)

HEAVY SIGH! :-(
That was too quick and easy. :-p
I just spend an hour trying to do it with R1C1 instead of A1 etc.
Thanks Hans. :-)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•