Duplicate excelsheet by clicking a specific cell in excel. Possible?

iscon

New Member
Joined
Mar 6, 2019
Messages
2
Hi everyone,

First I must say that this forum is awesome. Everybody wants to help!

So to my question. Is it possible to create a macro that makes it possible to duplicate the activated sheet by clicking a cell (Something like a "hyperlink"). Is this possible to do? I would be grateful if someone took a look at it.

/V
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,280
Office Version
365
Platform
Windows
How about
Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   If Target.Address(0, 0) = "A1" Then
      Cancel = True
      Sh.Copy , Sheets(Sh.Index)
   End If
End Sub
This needs to go in the ThisWorkbook module & will copy the active sheet if you double-click A1
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Double click on Range("A1") and this script will make a duplicate of the sheet

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  3/7/2019  10:46:43 AM  EST
Cancel = True
Dim ans As String
ans = ActiveSheet.Name
If Not Intersect(Target, Range("A1")) Is Nothing Then
Sheets(ans).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "New " & ActiveSheet.Index
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,100,190
Messages
5,473,034
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top