Step by Step help creating a link

Closed
ellamm50 Posts 3 Registration date Tuesday July 22, 2014 Status Member Last seen July 22, 2014 - Jul 22, 2014 at 04:58 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jul 24, 2014 at 11:05 AM
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!!

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 24, 2014 at 11:05 AM
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
0