Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ian
I am trying to semi automate the creation on a new worksheet within a book. I have recorded a macro which copies a master worksheet. I would like to offer users a button to initiate the macro and ideally have the new sheet given a name based on the content of the cell highlighted when the macro is run. any ideas? Ian -- ihr ------------------------------------------------------------------------ ihr's Profile: http://www.excelforum.com/member.php...o&userid=18944 View this thread: http://www.excelforum.com/showthread...hreadid=508944 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ian
right click sheet tabview code copy/paste this. chg column to your list
double click on the typed in name in the cell desired in column k Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column < 11 Then Exit Sub Sheets.Add ActiveSheet.Name = Target End Sub -- Don Guillett SalesAid Software "ihr" wrote in message ... I am trying to semi automate the creation on a new worksheet within a book. I have recorded a macro which copies a master worksheet. I would like to offer users a button to initiate the macro and ideally have the new sheet given a name based on the content of the cell highlighted when the macro is run. any ideas? Ian -- ihr ------------------------------------------------------------------------ ihr's Profile: http://www.excelforum.com/member.php...o&userid=18944 View this thread: http://www.excelforum.com/showthread...hreadid=508944 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ian
for a master worksheet (assume named Master) it would be
Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Range, Cancel As Boolean) If Target.Column < 11 Then Exit Sub With ThisWorkbook .Worksheets("Master").Copy After:=.Worksheets( _ .Worksheet.count) End With ActiveSheet.Name = Target End Sub -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... right click sheet tabview code copy/paste this. chg column to your list double click on the typed in name in the cell desired in column k Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column < 11 Then Exit Sub Sheets.Add ActiveSheet.Name = Target End Sub -- Don Guillett SalesAid Software "ihr" wrote in message ... I am trying to semi automate the creation on a new worksheet within a book. I have recorded a macro which copies a master worksheet. I would like to offer users a button to initiate the macro and ideally have the new sheet given a name based on the content of the cell highlighted when the macro is run. any ideas? Ian -- ihr ------------------------------------------------------------------------ ihr's Profile: http://www.excelforum.com/member.php...o&userid=18944 View this thread: http://www.excelforum.com/showthread...hreadid=508944 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ian
Thanks :) Tom - the master copy works great - needed to add an s ... worksheet*s*.count final copy in use also exits if target is blank... Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Range, Cancel As Boolean) If Target.Column < 22 Then Exit Sub If Target = "" Then Exit Sub With ThisWorkbook .Worksheets("Master_BOM").Copy After:=.Worksheets(.Worksheets.Count) End With ActiveSheet.Name = Target End Sub Ia -- ih ----------------------------------------------------------------------- ihr's Profile: http://www.excelforum.com/member.php...fo&userid=1894 View this thread: http://www.excelforum.com/showthread.php?threadid=50894 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|