Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple worksheets (Office 2003)

    I have a file with approximately 100 worksheets. On every worksheet, there is a formula cell calulating totals. I need a way to make a list the value of this one cell from every worksheet.

    Thanks,
    Julie

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

    Re: Multiple worksheets (Office 2003)

    Welcome to Woody's Lounge!

    Let's say that you want to create the list in a sheet called Summary, and that you want to list the value of cell C37 from all other worksheets.
    You could run the following macro to create the list.
    Warning: the macro will delete all existing data in columns A and B of the Summary sheet.

    Sub MakeList()
    Dim wshS As Worksheet
    Dim wsh As Worksheet
    Dim n As Long
    Set wshS = Worksheets("Summary")
    wshS.Range("A:B").ClearContents
    wshS.Range("A1") = "Worksheet"
    wshS.Range("B1") = "Value"
    wshS.Range("A1:B1").Font.Bold = True
    n = 1
    For Each wsh In Worksheets
    If Not wsh.Name = "Summary" Then
    n = n + 1
    wshS.Range("A" & n) = wsh.Name
    wshS.Range("B" & n) = wsh.Range("C37")
    End If
    Next wsh
    End Sub

  3. #3
    New Lounger
    Join Date
    Dec 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple worksheets (Office 2003)

    Thanks so much - that worked perfectly!

Posting Permissions

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