Step by Step help creating a linkClosed

Question

Good afternoon all,

I would like to create a link in a cell that will create a new worksheet in the same workbook. I am new to VBA and Macros so I need Step by step directions.

Example: If I type Ellen McKay in cell A5 in worksheet 1 it will open up an invoice template I created and insert the invoice in worksheet 2 of the same workbook as worksheet 1.

Thank you in advance for your assistance!!

TrowaD · Answer

Hi Ellamm,

Let me tell you what the code below will do, so you can see if it is what you want.

Requirements:
You will only use cell A5 on sheet1 to create new sheets and have a sheet named "Invoice template".

What the code will do:
For example you enter "Ellen McKay" in cell A5.
The code will automatically copy the sheet "Invoice template", rename it "Ellen McKay" and place the sheet before the last one (which I presume is the "Invoice template" sheet).

To make this work:
Select and copy the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub

Sheets("Invoice template").Copy After:=Sheets(Sheets.Count - 1)
ActiveSheet.Name = Target

End Sub
Right-click on sheet1's tab and select "View code".
A new window (Microsoft Visual Basic) appears.
Paste the code in the big white field.
You can now close the "Microsoft Visual Basic" window.

Testing time:
Back at Excel, try entering something in cell A5 of sheet1. After confirming your entry, you will notice a sheet is added with the same setup as the "Invoice template" sheet, but named after the value entered in A5.


Let us know if further explanation or adjustments are desired.

Best regards,
Trowa

Step by Step help creating a link

1 response

Similar discussions

Subscribe To Our Newsletter!