Ian

[ihr]

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

[Don Guillett]

right click sheet tab>view 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
[email protected]
"ihr" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> right click sheet tab>view 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
> [email protected]
> "ihr" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

[ihr]

Thanks

Tom - the master copy works great - needed to add an s ...

worksheets.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



Ian