Automatically rename new Sheet

[happydonut]

Hi,

I have a request and was hoping someone could help.

I have a Macro that turns data in Excel to a Pivot table.

When that is done, I can double click on a value in the Pivot Table, and a more detailed view shows. At the same time, a new Sheet gets created and gets opened. Is it possible to rename this new Sheet automatically? Preferably to the value on the same row, see example:

	Column A	Column B
Row 1	Salary	100 000

(When double clicking the amount, it will open a new Sheet, which should named Salary).

 

[Dan_W]

So the code below is pretty basic, but what it does is create a new sheet in the relevant workbook when you double click in a cell. The sheet name will be whatever is the contents of the cell you double-clicked. It needs to be added to the ThisWorkbook module (link).

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim NewName As String, NewWS As Worksheet
NewName = Target.Value
Set NewWS = ThisWorkbook.Sheets.Add
NewWS.Name = NewName
End Sub

I should stress that there are a number of problems with using this code as-is. There should be some form of error-handling to deal with a variety of foreseeable problems - what if you happen to double-click on an empty cell? what if there already exists a sheet with the same name? etc. I would add that this double-click function should be limited to only a certain part of a certain sheet (i.e., the range of the pivot table).

Anyway, this at least demonstrates that it's doable.

 

[happydonut]

Thanks for reply.

Well, I want the sheet name to be named after a certain cell on the same row as I double click.
As my example above, if I double click on the cell with the value 100 000, I want the Sheet name that has been created to automatically named Salary (e.g. the cell left to the one I doubleclick)

 

[Dan_W]

Ok. Try this:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim NewName As String, NewWS As Worksheet
NewName = Target.Offset(0,-1).Value
Set NewWS = ThisWorkbook.Sheets.Add
NewWS.Name = NewName
End Sub

 

[Dan_W]

Any luck?