Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating list from sheets (2003)

    I have sixtyeight sheets with information on. Cells C2 have the name of my servers, C6 the function and C16 the IP Address. I need to put all the server names in a list on a final sheet, the function of that server in the column next to it and finally the IP Address in the third column next to that. I was hoping I could do some sort of function to grab the details but I cannot get anything to work. Looks like I am left with a macro. Any takers?

    Neil
    Neil Eustice
    Woody Worshipper

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

    Re: Creating list from sheets (2003)

    Something like this? It assumes that the first 68 worksheets in your workbook contain server info, and that you already created a 69th blank sheet. You can adapt the code to your needs, add bells and whistles etc.

    Sub ListServers()
    Dim i As Integer
    For i = 1 To 68
    Worksheets(69).Range("A" & i) = Worksheets(i).Range("C2")
    Worksheets(69).Range("B" & i) = Worksheets(i).Range("C6")
    Worksheets(69).Range("C" & i) = Worksheets(i).Range("C16")
    Next i
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating list from sheets (2003)

    Having a brain dead day today. I get a subscript out of range when I try this. Each of my sheets has a name rather than sheet1 etc. I am sitting in the last sheet. What am I doing wrong?
    Neil Eustice
    Woody Worshipper

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

    Re: Creating list from sheets (2003)

    The code I posted does not use the worksheet names; it just loops through the first 68 worksheets in the workbook, and dumps results in the 69th worksheet. This will bomb if your workbook doesn't have 69 worksheets. Do you have 68+1 sheets in one workbook, or are they in separate workbooks?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating list from sheets (2003)

    I knew yesterday was a brain dead day - I had miscounted my sheets. Works perfectly now that I have amended the code for the correct number.

    Thanks so much. Saved me ages.
    Neil Eustice
    Woody Worshipper

  6. #6
    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 list from sheets (2003)

    Instead of counting them, let excel do it at tuntime (eg):

    <pre>Sub ListServers()
    Dim i As Integer
    Dim iCount As Integer
    iCount = worksheets.count
    For i = 1 To icount-1
    Worksheets(icount).Range("A" & i) = Worksheets(i).Range("C2")
    Worksheets(icount).Range("B" & i) = Worksheets(i).Range("C6")
    Worksheets(icount).Range("C" & i) = Worksheets(i).Range("C16")
    Next i
    End Sub</pre>


    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating list from sheets (2003)

    <hr>Instead of counting them, let excel do it at tuntime<hr>
    Or at funtime, or lun(ch)time, or maybe even at runtime! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

  8. #8
    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 list from sheets (2003)

    Fat-fingers strike again. Of course I meant "funtime". The "f" being very close the the "t" key. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating list from sheets (2003)

    Thanks for this - I have used it many times now and it works brilliantly.
    Neil Eustice
    Woody Worshipper

Posting Permissions

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