New Sheets Automatically use template and assigned to a new number

**CYRUSJ** · 09-18-2015, 02:03 PM

Hi there,

First time poster, longtime fan.

Goal:
*Create a purchase order log with a hyperlinked table of contents that when someone presses new tab a template pops up for them to fill.
*The new template automatically populates a hyperlink in the table of contents.
*Each new template sheet has a purchase order number assigned to it (ex. 0002) as the name of the sheet.

Any guidance would be really appreciated. I've been spinning my wheels here and have not gotten too far. Thank you!

**Gregor y** · 10-07-2015, 05:37 PM

double click the ThisWorkbook and update the NewSheet event code:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim ws As Worksheet: Set ws = Sh
    'code to copy and paste from template to the new ws
    
    'use the Table Of Contents(TOC) to get the next available PO number
    
    'code to update the TOC with the new PO sheet info/link
End Sub

Although I do not recomend this for anything but a very small company since excel does not handle multiple users well. You'd be much better off with Front-end and Back-end MS-Access databses if more than one person is to use the file/data.

**cjahanian** · 10-07-2015, 06:11 PM

Thanks for your response. I agree that using a macro with multiple users is awkward because you cannot share the workbook across the network. What is Front-end, Back-end MS-Access?

Attached is a copy of what I've gotten so far. Fairly happy with it, now I just need the columns in the table of contents to populate automatically. Any thoughts to have this constantly upload?

Thanks again!

P.O. Log Copy.xlsm

**Gregor y** · 10-08-2015, 08:43 PM

here's my 2¢

**Gregor y** · 10-08-2015, 08:49 PM

oh, and a front-end/back-end is when you split a MS-Access database into two seprate database files, the one with all the data tables lives on the network, and each user has a copy of the other one where all the queries and GUI live.

That way if someone gets locked up on their front end that that doesn't hose everyone else on the back end.

**cjahanian** · 10-14-2015, 11:43 AM

Gregory,

Thank you so much. Seriously this helps more than you know, I don't know how you stumbled across this post specifically but I'm very happy you did.

Very last thing is I cant figure out how to change the order of the log so the most recent (highest number) is at the top of the list.

**Gregor y** · 10-14-2015, 04:59 PM

I troll for posts no one else wants to touch: 2+ weeks and 0 replies...

you should be able to sort your current list A to Z on the po column, and then replace the button code with this updated version:

'only run via clicking on the new po button
Private Sub NewPO_Bttn_Click()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Template.Visible = xlSheetVisible
    Template.Copy After:=PO_Log
    Template.Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Set ws = Worksheets("  (template) (2)")
    Dim NextPO As Long: NextPO = WorksheetFunction.Min(PO_Log.Columns(POcol)) - 1
    Dim NextPOrow As Long ': NextPOrow = PO_Log.Cells(PO_Log.Rows.Count, POcol).End(xlUp).Offset(1, 0).Row
    Application.CutCopyMode = False
    PO_Log.Rows(3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    NextPOrow = 3
    Dim NextPOhl As String: NextPOhl = "HL_" & Abs(NextPO)

    'Build worksheet from the template
    On Error GoTo IncPO
    ws.Name = Replace(Template.Name, "template", Abs(NextPO))
    On Error GoTo 0
    ws.Range("A3").Name = NextPOhl
    ws.Hyperlinks.Add _
        Anchor:=ws.Range("A3"), Address:="", _
        SubAddress:=NavHL, TextToDisplay:=NavHLtxt

    'Log It
    PO_Log.Cells(NextPOrow, JobsiteCOL).Value = "=SUBSTITUTE('" & ws.Name & "'!I4,""-"","""")*1"
    PO_Log.Hyperlinks.Add _
        Anchor:=PO_Log.Cells(NextPOrow, POcol), Address:="", _
        SubAddress:=NextPOhl, TextToDisplay:=ws.Name
    PO_Log.Cells(NextPOrow, RegionCOL).Formula = "='" & ws.Name & "'!C4"
    PO_Log.Cells(NextPOrow, SellerCOL).Formula = "='" & ws.Name & "'!B7"
    PO_Log.Cells(NextPOrow, DescCOL).Formula = "='" & ws.Name & "'!E17"
    PO_Log.Cells(NextPOrow, RequiredDteCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!C13),"""",'" & ws.Name & "'!C13)"
    PO_Log.Cells(NextPOrow, RequestedDteCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!G13),"""",'" & ws.Name & "'!G13)"
    PO_Log.Cells(NextPOrow, AmountCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!J83),"""",'" & ws.Name & "'!J83)"
    PO_Log.Cells(NextPOrow, CompCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!N83),"""",'" & ws.Name & "'!N83)"
EOSb: Exit Sub
IncPO:
    NextPO = NextPO - 1
    Resume
End Sub

**cjahanian** · 10-15-2015, 11:05 AM

Gregory,

The very last thing you could hook me up with is correcting the hyperlink that appears on the TOC.

Right now any new item created will have a broken hyperlink, until I update the log by pressing F5 in the VBA Editor.

Again, thank you for the time you've put aside for this

**Gregor y** · 10-15-2015, 01:16 PM

looking at the Formulas(tab)>Defined Names(group)>Name Manager(button), it looks like the names for the work book are a little fubar from previous work.

adding this on at the bottom of the code and running it via F5 should clean them up/reset them:

Private Sub CleanupNames()
    Dim n As Name, ws As Worksheet
    For Each n In ThisWorkbook.Names: n.Delete: Next n
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = PO_Log.Name Then
            ws.Range("A1").Name = NavHL
        ElseIf ws.Name <> Template.Name Then
            ws.Range("A3").Name = "HL_" & Abs(ws.Name)
        End If
    Next ws
End Sub

after that i believe the links should be working again.

**cjahanian** · 10-27-2015, 02:29 PM

Purchase Order Log.xlsm

Hi Gregory,

Thank you so much for helping put this together. It was running smoothly for a while except today I heard reports of it hiccuping. The New PO button does not work properly and any new sheet is named as (Template (#)).

Attached is the most updated file. I'm sure its a quick fix, but I couldnt find my way around your code.

**Gregor y** · 10-28-2015, 12:56 PM

sorry i've been offline for a bit,

'only run via clicking on the new po button
Private Sub NewPO_Bttn_Click()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Template.Visible = xlSheetVisible
    Template.Copy After:=PO_Log
    Template.Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Set ws = Worksheets("  (template) (2)")
    Dim NextPO As Long: NextPO = WorksheetFunction.Min(PO_Log.Columns(POcol)) - 1
    'also moved this higher in the order
    On Error GoTo IncPO
    ws.Name = Replace(Template.Name, "template", Abs(NextPO))
    On Error GoTo 0

    Dim NextPOrow As Long ': NextPOrow = PO_Log.Cells(PO_Log.Rows.Count, POcol).End(xlUp).Offset(1, 0).Row
    Application.CutCopyMode = False
    PO_Log.Unprotect
    PO_Log.Rows(3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    NextPOrow = 3
    Dim NextPOhl As String: NextPOhl = "HL_" & Abs(NextPO)

    'Build worksheet from the template
    ws.Range("A3").Name = NextPOhl
    ws.Hyperlinks.Add _
        Anchor:=ws.Range("A3"), Address:="", _
        SubAddress:=NavHL, TextToDisplay:=NavHLtxt

    'Log It
    PO_Log.Cells(NextPOrow, JobsiteCOL).Value = "=SUBSTITUTE('" & ws.Name & "'!I4,""-"","""")*1"
    PO_Log.Hyperlinks.Add _
        Anchor:=PO_Log.Cells(NextPOrow, POcol), Address:="", _
        SubAddress:=NextPOhl, TextToDisplay:=ws.Name
    PO_Log.Cells(NextPOrow, RegionCOL).Formula = "='" & ws.Name & "'!C4"
    PO_Log.Cells(NextPOrow, SellerCOL).Formula = "='" & ws.Name & "'!B7"
    PO_Log.Cells(NextPOrow, DescCOL).Formula = "='" & ws.Name & "'!E17"
    PO_Log.Cells(NextPOrow, RequiredDteCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!C13),"""",'" & ws.Name & "'!C13)"
    PO_Log.Cells(NextPOrow, RequestedDteCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!G13),"""",'" & ws.Name & "'!G13)"
    PO_Log.Cells(NextPOrow, AmountCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!J83),"""",'" & ws.Name & "'!J83)"
    PO_Log.Cells(NextPOrow, CompCOL).Formula = "=IF(ISBLANK('" & ws.Name & "'!N83),"""",'" & ws.Name & "'!N83)"
EOSb:
    PO_Log.Protect
    Exit Sub
IncPO:
    NextPO = NextPO - 1
    Resume
End Sub

New Sheets Automatically use template and assigned to a new number

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Re: New Sheets Automatically use template and assigned to a new number

Thread Information

Users Browsing this Thread

Similar Threads

Row Numbering (First four of same number assigned 1, remaining of number assigned 10000)

Automatically pull value from assigned cell into text box in graph

[SOLVED] Copy Template Worksheet to Multiple Worksheets and Incrementally Number Sheets in Workbook

automatically update a number in cell when template opens

how can you re-number multiple sheets 'automatically'?

[SOLVED] How do I change the invoice number assigned in Invoice template...

Automatically number multiple sheets

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions