VBA to copy master sheet and insert new line on summary sheet with updated formulas

[Scott 2020]

Hi,

New to this forum and had a good look through the threads to try and find a solution and I'm sure it is here albeit over several threads but I'm having trouble piecing the VBA together to make this work.

Background: I'm creating a customer account management database which consists of x1 worksheet per customer which is linked to a master 'Summary' tab for the purposes of seeing at a glance outstanding payments by customer.

Objective: I've created a Command Button which when pressed I hope to i) copy the "Blank" template, ii) re-name it to the next sequential account number in the "Summary" tab, iii) move the new worksheet to the end, iv) insert a new line on row 6 of the 'Summary' tab for the new worksheet created complete with updated formulas to pull the data through.

I've created some code and managed to get it all working with the exception that it copies the last worksheet (complete with the customer information) rather than the "Blank" worksheet. To prevent confusion (and to hide my embarrassment, I'm a nube!) I'll not post the code. I have however attached the file.

Any help on writing the VBA code to achieve the objective would be most appreciated.

Scott

[pipoliveira]

Hey Scott,
Welcome to ExceForum.
On the new sheet, do you want to create it with the account number or rather the customer name?
It is possible to do what you want, but I would suggest to create first an account using a UserForm that after completed, the new account gets updated in the Summary sheet and also creates a new sheet for that customer.
Let me know and I'll do it for you.
Also, to protect the blank sheet and use it as a template, you should hide it.
Cheers,
Filipe

[pipoliveira]

Hi Scott,

Please check attached file.

I've added a UserForm when you click on "New Customer" button.

On the form, you can add all details from new customer but it is not mandatory. If you just click on the "ADD" button, it will create a mew account (1502); add a new sheet with the new account number and copy the formulas to the summary sheet.

The link for the new created sheet will also be active.

I took the liberty to create a Conditional Formatting so any customer that is added, the table get formatted as well.

On the code, I have put some notes of what the code is doing for your future reference and so you can also understand what the code does.

Don't worry with the blank sheet is it is very hidden to avoid deletion.

Please let me know if that's what you're looking for.

Best regards,
Filipe

[Scott 2020]

Filipe, you are a genius! Works a treat. Many thanks for your help

Regards,
Scott

[pipoliveira]

Thanks Scott for the feedback and rep

Just one more thing, if you want to start a new table without data/accounts, you should add the following code to start the account number on "1501". Change this on the outlined part if you want to start with other numeration (This code to be added on the UserForm_Activate):

Private Sub UserForm_Activate()
'Code to start Account Number if table is empty. Account set to start at "1501". Change below this number as desired.
If Sheets("Summary").Range("A6") = "" Then
    Me.AccountTxB.Caption = "1501"
ElseIf Sheets("Summary").Range("A6") <> "" Then
    Me.AccountTxB.Caption = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Value + 1
End If

End Sub

Best of luck!
Filipe Oliveira