Prompt user to select row#, then copy template to a new sheet and name it based on cells from the user-selected row

[mkseto]

1st sheet is "Master", which is a list of accounts, and new accounts are being created by users on a regular basis. Each row represents one record/account.
2nd sheet is "Template", which is pre-filled with formulas and pre-formatted cells. *** "Template" will be a hidden sheet
TO create/add a new account, user would enter account details in the last unused row. Only 4 fields will be entered (into cells A to D):
A = Client number (up to 9 digits)
B = Client name (alpha)
C = Account ID (up to 5 digits)
D = Account Number (up to 7 digits)
***** a client may have multiple accounts, so A and B are not unique, but the C+D combination is unique in the account list
The macro I need:
- After user enters a new account (i.e. A-D in a new row), they will click a button to activate the macro (to do the following)
- Checks to see if new account just entered (i.e. last row) already exists in rows above by checking the C + D combination (e.g. "12345 1234567"). Display error message if already exist, and clears last row data
- Inserts a new sheet by making a copy of the "Template", and name it using the values entered in C and D (i.e. "12345 1234567"), placed after "Master" (in other words, newest sheet will always be the 2nd visible sheet.

It would be a bonus is the following can also be added to the macro:
- the cell "E" of the newly added row/account in "Master" be linked to cells W5 of the newly created sheet. For example, if user entered new account in the 7th row in "Master", the value in "E7" will be a lookup of value in "W5" of the newly created sheet.

Hope someone can help.

 

[DanteAmor]

Try this

Sub CopyTemplate()
  Dim shM As Worksheet, shT As Worksheet
  Dim lr As Long, n As Long
  Dim aID As Variant, aNM As Variant
  
  Set shM = Sheets("Master")
  Set shT = Sheets("Template")
  
  lr = shM.Range("A:D").Find("*", , xlValues, , xlByRows, xlPrevious).Row
  n = WorksheetFunction.CountIfs(shM.Range("C1:C" & lr), shM.Range("C" & lr).Value, shM.Range("D1:D" & lr), shM.Range("D" & lr).Value)
  aID = shM.Range("C" & lr).Value
  aNM = shM.Range("D" & lr).Value
  If aID = "" Or aNM = "" Then
    MsgBox "Missing Account ID or Account Number", vbCritical, "VALIDATIONS"
    Exit Sub
  End If
  If n > 1 Then
    MsgBox "Account ID + Account Number, already exist: " & aID & " + " & aNM, vbCritical, "VALIDATIONS"
    shM.Range("A" & lr & ":D" & lr).Value = ""
    Exit Sub
  End If
  
  Application.ScreenUpdating = False
  shT.Visible = xlSheetVisible
  shT.Copy after:=shM
  With ActiveSheet
    .Name = aID & " " & aNM
    shM.Hyperlinks.Add shM.Range("E" & lr), "", "'" & .Name & "'!W5", , "id " & aID & " " & aNM
  End With
  shT.Visible = xlSheetHidden
  shM.Select
  Application.ScreenUpdating = True
End Sub

 

[mkseto]

Thank you, thank you, thank you!!!! This is amazing!
One issue I ran into: the macro creates a new sheet and names it correctly, but cell "E" is populated with "id xxxxx xxxxxxx" (i.e. characters "id" followed by the name of the new sheet) instead of the value in W5 (of the new sheet). Could that be an easy fix?
*** by the way, the value in W5 will be blank until user updates the value, which could be updated again later. I'm hoping the value will always be up-to-date in the Summary sheet cell "E".

Thanks you again!!!!

 

[mkseto]

Try this

..................

Andy my apology for using the wrong term "lookup" for cell "W5", I should have said "link" instead. Sorry.

 

[DanteAmor]

by the way, the value in W5 will be blank until user updates the value, which could be updated again later. I'm hoping the value will always be up-to-date in the Summary sheet cell "E".

Try this:

Sub CopyTemplate()
  Dim shM As Worksheet, shT As Worksheet
  Dim lr As Long, n As Long
  Dim aID As Variant, aNM As Variant
  
  Set shM = Sheets("Master")
  Set shT = Sheets("Template")
  
  lr = shM.Range("A:D").Find("*", , xlValues, , xlByRows, xlPrevious).Row
  n = WorksheetFunction.CountIfs(shM.Range("C1:C" & lr), shM.Range("C" & lr).Value, shM.Range("D1:D" & lr), shM.Range("D" & lr).Value)
  aID = shM.Range("C" & lr).Value
  aNM = shM.Range("D" & lr).Value
  If aID = "" Or aNM = "" Then
    MsgBox "Missing Account ID or Account Number", vbCritical, "VALIDATIONS"
    Exit Sub
  End If
  If n > 1 Then
    MsgBox "Account ID + Account Number, already exist: " & aID & " + " & aNM, vbCritical, "VALIDATIONS"
    shM.Range("A" & lr & ":D" & lr).Value = ""
    Exit Sub
  End If
  
  Application.ScreenUpdating = False
  shT.Visible = xlSheetVisible
  shT.Copy after:=shM
  With ActiveSheet
    .Name = aID & " " & aNM
    shM.Hyperlinks.Add shM.Range("E" & lr), "", "'" & .Name & "'!W5", , ""
    shM.Range("E" & lr).Formula = "=IF('" & .Name & "'!W5="""",""Link"",'" & .Name & "'!W5)"
  End With
  shT.Visible = xlSheetHidden
  shM.Select
  Application.ScreenUpdating = True
End Sub

 

[mkseto]

Thank you again, you are AMAZING!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.