Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Placing Worksheet Names into cells

    Using Excel 97 SR2

    I need <img src=/S/help.gif border=0 alt=help width=23 height=15> with a problem <img src=/S/question.gif border=0 alt=question width=15 height=15>

    I have a worksheet that summarizes information from all other worksheets within the workbook. Is it possible to have a piece of code place the name of each worksheet in a cell on the summary worksheet. One other thing, I would like to ignore some of the worksheet names (not have the name placed into a cell).

    For example, here are the names of the worksheets in my workbook (these vary from workbook to workbook):

    <table border=1><td><font color=red>EntryCodes</font color=red></td><td>Smith</td><td>Jones</td><td>Thomas</td><td>Peterson</td><td><font color=red>Blank</font color=red></td><td>Williams</td><td>Johnson</td><td>Harris</td><td>Stevens</td><td><font color=red>Update</font color=red></td><td>Keebler</td><td>Howard</td><td>Spencer</td><td>Brickley</td></table>

    I am looking for the code to ignore the worksheets in <font color=red>red</font color=red>, and list the rest of the names in any order starting with cell $A$5. The first name would be placed in $A$5, the second in $A$6, the third in $A$7, and so on. Cell $A$40 would be the last cell populated.

    Any ideas are appreciated. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

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

    Re: Placing Worksheet Names into cells

    Something like this should work:

    <pre>Public Sub StoreSheetNames()
    Dim oSheet As Worksheet
    Dim I As Integer
    I = 0
    For Each oSheet In Worksheets
    If oSheet.Name <> "EntryCodes" And oSheet.Name <> "Blank" _
    And oSheet.Name <> "Update" Then
    Sheet1.Range("A5").Offset(I, 0) = oSheet.Name
    I = I + 1
    End If
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Placing Worksheet Names into cells

    Thank you again, for your help.

    With one minor change, your code did exactly what I needed. I need to change the following line:

    Sheet1.Range("A5").Offset(I,0)=oSheet.Name
    to
    Sheets("Update").Range("A5").Offset(I,0)=oSheet.Na me

    Thanks again. <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

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