2

I have a pivot table in excel which looks like this:

Team         Doc 1  Doc 2   Grand Total
Team A       13     12      25
Team B       8      7       15
Team C       32     5       37
Grand Total  53     24      77

I have already written a piece of VBA which will format any drill down sheets for printing (Workbook_NewSheet(ByVal Sh As Object)). However, as I'm trying to make this as user friendly as possible, I'd really like to be able to use vba to automatically rename any worksheets generated from the pivot table. However, I'm not sure how to do it as the content of each worksheet will be different depending on where the user clicks (i.e. if the user clicks in Team A Doc 1 Total then the sheet should be named 'Team A Doc 1' but if the user clicks in Grand Total row of Doc 2 then the sheet should be named 'Grand Total Doc 2') - I think there are something like 15 different worksheet names that could occur which is why I'm guessing the worksheet defaults to Sheet1! I'm thinking that a name could be generated by using offset to pick up the team name or the column name based on the active cell but I'm not really sure where to start so any suggestions/assistance would be greatly appreciated!

Thanks

Community
  • 1
  • 1
bawpie
  • 467
  • 4
  • 12
  • 27
  • In the NewSheet Event, locate the cell that contains the name you want to change the sheet name to. Then change the sheet name using the cell's value. If you're lucky, "the cell that contains the name you want to change the sheet name to" will always be the same cell, so you can just tell Excel to rename the sheet to whatever value is in that cell. – JimmyPena Aug 02 '12 at 13:19
  • I see you've got a solution. This excellent post about coding around pivot table ranges should still be of interest: `http://peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba/` – Doug Glancy Aug 02 '12 at 13:46
  • Thanks @DougGlancy, peltiertech is an excellent site in general but I don't think I've come across this article before so I'll be sure to check it out. – bawpie Aug 02 '12 at 13:49

2 Answers2

2

I wish I could comment, but I can't yet, as I have not enough rep points! (Had to restart my account!)

I can suggest that you record a macro while you do a drill down on any given data point manually, and see how the recorded vba code looks. I would think from there you can configure your code to base the name of your worksheet on some element of the recorded code.

Since, I wanted this to be a comment, I will delete this if it's not helpful.

Update To Your Newly Posted Answer:

To check if the sheet already exists when a user drills down, you can check if the sheet existss after you get the sheet name to and if it does, select it, rather than creating a new one. Otherwise, you create it.

See this code for that:

Private Sub Workbook_NewSheet(ByVal sh As Object)

Application.ScreenUpdating = False

Dim shtCur As Worksheet
Set shtCur = ActiveSheet

Sheets("DQ Summary").Select
RN = ActiveCell.Row
CN = ActiveCell.Column
SheetName = Cells(RN, 2).Value & " - " & Cells(9, CN).Value


If SheetExists(SheetName) Then
    Worksheets(SheetName).Select
Else

    shtCur.Move _
        After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
    shtCur.Name = "SheetName"
End If


Application.ScreenUpdating = True


End Sub

Function SheetExists(wsName As String, Optional wb As Workbook = Nothing) As Boolean

SheetExists = False
Dim WS As Worksheet

If wb Is Nothing Then Set wb = ThisWorkbook

On Error Resume Next
Set WS = wb.Worksheets(wsName)
On Error GoTo 0

If Not WS Is Nothing Then SheetExists = True

End Function
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Thanks for the update, I think we both came up with solutions though yours looks a little more elegant! – bawpie Aug 02 '12 at 14:12
  • 1
    Yes, and I was really posting more to help you solve the fact that the drill down may have been done already, and how to avoid the sheet being created / named twice. – Scott Holtzman Aug 02 '12 at 14:23
1

I've managed to come up with something fairly workable:

Private Sub Workbook_NewSheet(ByVal sh As Object)

Dim RN, CN As Byte
Dim SheetName As String

Application.ScreenUpdating = False

ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

'Names the sheet according to the pivot drill

Sheets("DQ Summary").Select
RN = ActiveCell.Row
CN = ActiveCell.Column
SheetName = Cells(RN, 2).Value & " - " & Cells(9, CN).Value

'Identifies if worksheet already exists and replaces it if so.
Application.DisplayAlerts = False
On Error Resume Next
mySheetNameTest = Worksheets(SheetName).Name
If Err.Number = 0 Then
Worksheets(SheetName).Delete
    MsgBox "The sheet named ''" & SheetName & "'' already exists but will be replaced."
Else
    Err.Clear
End If
Application.DisplayAlerts = True

Sheets(ActiveWorkbook.Sheets.Count).Select
ActiveSheet.Name = SheetName    

End Sub

Basically it's added onto the newsheet event - the macro adds the new sheet to the end of the workbook, goes to the pivot table sheet and identifies the column and row names of the active cell (since the column name and row name will always be static I can hard code this in) and then locates the newly added sheet (always at the end of the workbook) and renames it. Unfortunately there's an issue if a user tries to drill on the same data twice (can't have two worksheets with the same name) which I'm hoping to iron out.

Thanks for views/comments.

Edit: Updated code to work around worksheet duplication issue, seems to be doing the trick!

bawpie
  • 467
  • 4
  • 12
  • 27