Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Each sheet in book (XL-97 +)

    Ok it's Friday afternoon

    But how can I find the value of say cell A1 in worksheet of a workbook.

    I want to capture the value of A1 in an array so that I can pop them into a coluumn on a summary sheet. Every time I do for each worksheet(ws) in workbook(wb) I keep getting errors.

    If I don't hear from you, have a good weekend
    Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Each sheet in book (XL-97 +)

    Or this?
    <pre>Sub ArrayA1()
    Dim vArray()
    Dim i As Integer
    Dim iCount As Integer
    iCount = Worksheets.Count
    ReDim vArray(1 To iCount)
    For i = 1 To iCount
    vArray(i) = Worksheets(i).Range("a1")
    Next
    End Sub</pre>


    Steve

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

    Re: Each sheet in book (XL-97 +)

    Is this what you want:

    <pre>Dim oWS as Worksheet
    For Each oWS In WorkSheets
    MsgBox "A1 on " & oWS.Range("a1").Name & " = " oWS.Value
    Next oWS
    </pre>


    Edited by Legare Coleman to fix a typo.
    Legare Coleman

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Each sheet in book (XL-97 +)

    Thanks Steve

    Absolutely perfect. I am always getting confused with Worksheet and worksheets. One day I will get it right

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Posting Permissions

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