Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding info to summary and new sheets (Excel 2007)

    I have a workbook for purchase orders. The first sheet is the summary page. It lists the name of the PO sheet as a hyperlink to that sheet plus the description, total, date and status of each order. Each additional sheet is one purchase order. The sheets are labelled "PO # 001", "PO# 002", etc. The template has sheets for 20 purchase orders. I'm trying to add a button on the summary sheet that would add additional sheets when required. I think I've figured out the code for this part. However, I want to continue the naming process on the sheets so that after 20 would be "PO # 021". I haven't quite got that. The name is text but I want it to continue numerically. And then I want to add a row to the summary sheet, insert the name from the new worksheet, the hyperlink and the formulas that would link to the information in the PO sheet. Not so easy - at least for me. Maybe I need to create a user input window?? Any help would be appreciated. Thanks

    PS I'd download the file but I can't get to just the *.xls format in 2007. If anyone can tell me how I'll fix it. Thanks

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

    Re: Adding info to summary and new sheets (Excel 2007)

    Here is a macro that will create a new sheet, name it and create a hyperlink in column A in the summary sheet.

    Sub AddSheet()
    Dim strName As String
    Dim intNum As Integer
    Dim wsh As Worksheet
    Dim lngRow
    strName = Worksheets(Worksheets.Count).Name
    intNum = Val(Right(strName, 3))
    strName = "PO # " & Format(intNum + 1, "000")
    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    wsh.Name = strName
    With Worksheets("Summary")
    lngRow = .Range("A65536").End(xlUp).Row + 1
    .Hyperlinks.Add Anchor:=.Range("A" & lngRow), Address:="", _
    SubAddress:="'" & strName & "'!A1", TextToDisplay:=strName
    End With
    End Sub

    Without specific information I cannot tell which link formulas you need.

  3. #3
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding info to summary and new sheets (Excel 2007)

    Mostly this works. However, I created a blank PO template sheet that I need to be copied onto the new sheet so that each page is the same. So I'm thinking I should select the template sheet and then copy it, right?

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

    Re: Adding info to summary and new sheets (Excel 2007)

    Instead of the line

    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count) )

    use

    Worksheets("POTemplate").Copy After:=Worksheets(Worksheets.Count)
    Set wsh = Worksheets(Worksheets.Count)

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

    Re: Adding info to summary and new sheets (Excel 2007)

    Hans' code will do what you want as long as the sheet with the highest PO number is the last sheet in the workbook. However, if the user moves the sheets around so that the last sheet is not the highest numbered PO, then the code will fail. The modification below should prevent that possibility:

    <code>
    Sub AddSheet()
    Dim strName As String
    Dim intNum As Integer, intLastNum As Integer
    Dim wsh As Worksheet
    Dim lngRow
    For Each wsh In Worksheets
    If Left(wsh.Name, 4) = "PO #" Then
    strName = wsh.Name
    intNum = Val(Right(strName, 3))
    If intNum > intLastNum Then intLastNum = intNum
    End If
    Next wsh
    strName = "PO # " & Format(intLastNum + 1, "000")
    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    wsh.Name = strName
    With Worksheets("Summary")
    lngRow = .Range("A65536").End(xlUp).Row + 1
    .Hyperlinks.Add Anchor:=.Range("A" & lngRow), Address:="", _
    SubAddress:="'" & strName & "'!A1", TextToDisplay:=strName
    End With
    End Sub
    </code>
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding info to summary and new sheets (Excel 2007)

    Yippee works good. I'm learning buckets today. Now another question, in the summary I want to put a formula in the total cell which pulls the total from sheet "PO# 001" and places it in the corresponding row in the summary. So when I create the worksheet and the new row in the summary, I also need to enter a formula in the cell that would read "="PO# 001"!$H$39". The H39 would always remain a constant but the name of the worksheet would change with the page addition. I'm thinking I add the strName to the formula?

  7. #7
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding info to summary and new sheets (Excel 2007)

    Even more brilliant since I know somebody's gonna mess with the order. It's their nature. Thanks!

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

    Re: Adding info to summary and new sheets (Excel 2007)

    You could insert the following line between With ... and End With:
    <code>
    .Range("B" & lngRow).Formula = "='" & strName & "'!$H$39"
    </code>
    This will put the formula in column B of the summary sheet.

  9. #9
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding info to summary and new sheets (Excel 2007)

    Works like a charm - thank you very much. Give yourself a big hug from me. Thanks, Mary

Posting Permissions

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