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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,847
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,102,695
Messages
5,488,328
Members
407,634
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top