Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Creating lists (XL97/WinNT4)

    I have a workbook with some 15 worksheets, and the last one (summary) needs to have in column A a list of all the values in column C of the other sheets from C3 down, no matter how many there are on each sheet (anything from a dozen to 350+), then in summary column B either the value in A1 of the sheet the entry in summary column A refers to, or the sheet name, whichever is easiest to retrieve (they're the same).

    I'm sure there must be a way to do this (VBA is fine, if necessary) but I haven't been able to figure it out!

    Thanks in anticipation
    Beryl M


  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: Creating lists (XL97/WinNT4)

    Does this do what you want?
    Change the summary sheet name as appropriate

    Steve

    <pre>Sub BerylMList()
    Dim wksSummary As Worksheet
    Dim wks As Worksheet
    Dim rng As Range
    Dim rngDest As Range

    Set wksSummary = Worksheets("Summary")
    For Each wks In Worksheets
    If wks.Name <> wksSummary.Name Then
    wks.Select
    Set rng = wks.Range(Range("c3"), Range("c65536").End(xlUp))
    wksSummary.Select
    Set rngDest = wksSummary.Range("a65536").End(xlUp).Offset(1, 0)
    rng.Copy rngDest
    Set rngDest = Range(rngDest, Range("a65536").End(xlUp)).Offset(0, 1)
    rngDest = wks.Name
    End If
    Next
    End Sub</pre>


  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Creating lists (XL97/WinNT4)

    That's brilliant, Steve, and does exactly what I wanted! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    As a side question, I'm using autofill to fill in some formulae against each line I get by running this function, but if I record the autofill it records it as destination and the exact range it's used, eg c2:01636, but I need it to autofill the current number of rows, no matter how many there are. Is there a way to do this? I tried clicking the relative button when recording but it still took the actual range...

    Thanks again!
    Beryl M


  4. #4
    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: Creating lists (XL97/WinNT4)

    You could use a variant of:
    Range("c22").AutoFill Destination:=Range(Range("c2"), Range("b65536").End(xlUp).Offset(0, 13))

    Though you might want to generalize it more with variables:

    lRow = 2
    iColStart = 3 'Col C
    iColEnd = 15 'Col O

    <pre>Range(Cells(lrow, iColStart), Cells(lrow, iColEnd)).AutoFill _
    Destination:=Range(Cells(lrow, iColStart), _
    Cells(65536, iColStart - 1).End(xlUp).Offset(0, iColEnd - iColStart + 1))
    </pre>


    Steve

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Creating lists (XL97/WinNT4)

    Ah - good thinking, Batman! That looks, eminently feasible - I shall experiment this afternoon!

    Ta again!
    Beryl M


Posting Permissions

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