Copy Sheet VBA Code To New Sheet Automatically

SGD78

New Member
Joined
Mar 24, 2016
Messages
31
Hello everyone,

I'm working on a workbook that tracks clients, and therefore need to create a new sheet for each new client. Does anyone know of a way to configure excel to copy the sheet vba code of an existing worksheet to a newly added worksheet automatically?

Any assistance on this would be greatly appreciated. Thanks to all in advance.

SGD.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
It is built into Excel

Right-click Sheet tab
\ select Move or Copy \ check Create a copy \ ok

Sheet and its code is copied :)
 
Last edited:
Upvote 0
here is a simple way to use VBA to achieve it

This code goes in the sheet module of sheet containing your list of clients
Assumes your cient names are in column A starting in row 2
Add new client in column A (and any other details in other columns)
Double click in client's cell in column A
"ModelSheet" & its code is copied
New named sheet is created

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row > 1 And Target.Column = 1 Then
        Cancel = True
        On Error Resume Next
        Sheets("ModelSheet").Copy after:=Me
        ActiveSheet.Name = Target
    End If
End Sub
 
Last edited:
Upvote 0
here is a simple way to use VBA to achieve it

This code goes in the sheet module of sheet containing your list of clients
Assumes your cient names are in column A starting in row 2
Add new client in column A (and any other details in other columns)
Double click in client's cell in column A
"ModelSheet" & its code is copied
New named sheet is created

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row > 1 And Target.Column = 1 Then
        Cancel = True
        On Error Resume Next
        Sheets("ModelSheet").Copy after:=Me
        ActiveSheet.Name = Target
    End If
End Sub

This worked marvelously. Thanks again for the help Yongle. :)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top