Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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"
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listing Data from Multiple Sheets (XP)

    Touche!

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listing Data from Multiple Sheets (XP)

    Thanks Braddy,
    Sounds like I better take a look at ASAP....

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

  12. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #14
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •