Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Naming Excel Sheets (Excel xp)

    I need to create worksheets where the name of the worksheet is in Range D730. Some of the cell are blank and some have a 2 digit number. Whatever the number is, i want to name the worksheets. If they are blank i don't want a worksheet to be added. I also need them in numerical order from left to right.

    Example: D7 has 01, D8 has 02, D9 has 03, D10 thru D15 are blank, D16 has 21, D17 has 22, and the rest are blank. I would like to add 5 worksheets called ws01, ws02, ws03, ws21, and ws22. Thank you very much for the help.

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

    Re: Naming Excel Sheets (Excel xp)

    This is should not be difficult to do, however we need a little more information first.

    1- Are the values in D730 numbers or text?

    2- Your example showed the values in sequence. Will this always be the case, or do the values need to be sorted?

    3- If they need to be sorted, can the sort be done on the worksheet, or must the sheet remain unchanged?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Naming Excel Sheets (Excel xp)

    The cells from D730 are numbers. They are not always in numerical order and they cannot be sorted on the same sheet. Sometimes the rows are in 2 or 3 different "categories" and within that category they are in numerical order but between categories they are not...

    Cheese
    Swiss 01
    Cheddar 10

    Soda
    Coke 05
    Pepsi 07
    Mt. Dew 13

    Thank you for the help

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming Excel Sheets (Excel xp)

    Will this do it for you?

    Sub CreateSheets()

    Dim MyCell
    Dim temp

    For Each MyCell In Selection

    If MyCell.Value = "" Then
    Else
    Sheets.Add
    On Error Resume Next 'skips all instances where name not valid
    ActiveSheet.Name = MyCell
    End If

    Next
    End Sub

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming Excel Sheets (Excel xp)

    Re my last post:

    1. Alt -F11
    2. insertModule
    3. paste the following:

    Sub CreateSheets()

    Dim MyCell
    Dim temp

    For Each MyCell In Selection

    If MyCell.Value = "" Then
    Else
    Sheets.Add
    On Error Resume Next 'skips all instances where name not valid
    ActiveSheet.Name = MyCell
    End If

    Next
    End Sub

    4. Goto sheet with potential names, and highlight the area that contains the names
    5. Run macro (toolsmacrosmacroscreatesheets -run)
    6. All sheets will be created, where the data in the cell is not valid the name will not be changed (so will stay as sheet12 )

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Naming Excel Sheets (Excel xp)

    hmmm, so simple. that's great! thanks a lot

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

    Re: Naming Excel Sheets (Excel xp)

    See if the code below will do what you want:

    <pre>Public Sub InsertWS()
    Dim oCell As Range, oWS As Worksheet
    Dim I As Integer, J As Integer, iMax As Integer, iWk As Integer
    Dim iVals(0 To 22) As Integer
    I = 0
    For Each oCell In Worksheets("Sheet1").Range("D730")
    If oCell.Value <> "" Then
    For J = 0 To I
    If iVals(J) = oCell.Value Then Exit For
    Next J
    If J > I Then
    iVals(I) = oCell.Value
    I = I + 1
    End If
    End If
    Next oCell
    If I = 0 Then Exit Sub
    iMax = I - 1
    For I = 0 To iMax - 1
    For J = I + 1 To iMax
    If iVals(I) > iVals(J) Then
    iWk = iVals(I)
    iVals(I) = iVals(J)
    iVals(J) = iWk
    End If
    Next J
    Next I
    For I = 0 To iMax
    Set oWS = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWS.Name = "ws" & Format(iVals(I), "00")
    Set oWS = Nothing
    Next I
    End Sub
    </pre>

    Legare Coleman

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

    Re: Naming Excel Sheets (Excel xp)

    I don't think it is quite that simple. You never name the sheets with the names in D730 and you never do anything to sort them into order.
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Naming Excel Sheets (Excel xp)

    yes, you are right. I tried your code and it works perfectly. So the array iVals is loaded with the numbers that appear in D730. Now I'd like to load text that appears in COL B7:B30 into the corresponding ws[number]. So on D7 there is a 1 and in B7 there is a text "ABC Co.", i now want to put ABC Co. in A1 of ws1 and so on. I can't tell you how much i appreciate your help. I have put all your ideas to good use and am learning that Excel is extremely powerful and flexible. Again, thank you very much.

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

    Re: Naming Excel Sheets (Excel xp)

    This should do it:

    <pre>Public Sub InsertWS()
    Dim oCell As Range, oWS As Worksheet
    Dim I As Integer, J As Integer, iMax As Integer, iWk As Integer
    Dim strWk As String
    Dim iVals(0 To 22) As Integer, strLbl(0 To 22) As String
    I = 0
    For Each oCell In Worksheets("Sheet1").Range("D730")
    If oCell.Value <> "" Then
    For J = 0 To I
    If iVals(J) = oCell.Value Then Exit For
    Next J
    If J > I Then
    iVals(I) = oCell.Value
    strLbl(I) = oCell.Offset(0, -2).Value
    I = I + 1
    End If
    End If
    Next oCell
    If I = 0 Then Exit Sub
    iMax = I - 1
    For I = 0 To iMax - 1
    For J = I + 1 To iMax
    If iVals(I) > iVals(J) Then
    iWk = iVals(I)
    iVals(I) = iVals(J)
    iVals(J) = iWk
    strWk = strLbl(I)
    strLbl(I) = strLbl(J)
    strLbl(J) = strWk
    End If
    Next J
    Next I
    For I = 0 To iMax
    Set oWS = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWS.Name = "ws" & Format(iVals(I), "00")
    oWS.Range("A1").Value = strLbl(I)
    Set oWS = Nothing
    Next I
    End Sub
    </pre>

    Legare Coleman

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Naming Excel Sheets (Excel xp)

    That's a good one and worked great. Is there a methodology you use when doing nested if/then and for/next statements. I can follow it when you send me the code but i can't necessarily do it by myself. Is there a method you use to think it through?

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

    Re: Naming Excel Sheets (Excel xp)

    The only method is to logically think through what you are trying to do.
    Legare Coleman

Posting Permissions

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