How to create a variable to pass value to worksheet name?

[drmingle]

I am using Excel 2010.

I am trying to pull the string of the SlicerItems that equal True and pass it to a worksheet name. I need some help trouble shooting the code below:

Sub SlicerName_Copy()
'
' Objective: To read what is selected by end-user with Slicer and copy the selection
'            to a new worksheet name. Another words where SlicerItems = True
'            pass SlicerItems value.
'
    With ActiveWorkbook.SlicerCaches("Slicer_PerID_NAME")
        .SlicerItems("Timmy").Selected = True
        .SlicerItems("Tommy").Selected = False
        .SlicerItems("Ronny").Selected = False
        .SlicerItems("Ralph").Selected = False
    End With
    ActiveWorkbook.SlicerCaches("Slicer_PerID_NAME").SlicerItems("Timmy").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Select
    ActiveSheet.Name = i
    Range("A1").Select
End Sub

Thanks in advance...

 

[Jerry Sullivan]

Hi drmingle,

The approach would be slightly different depending on whether there could be more than one slicer item selected, and if so, whether you want to run the same process on each (copy and rename a sheet) or just the first selected item.

If you'll clarify that, I'll suggest some code.

 

[drmingle]

JS411,

Thank you for responding to my post.

1. Yes, there could be more than one slicer item selected.
2. I only one sheet named no matter the number of slicer items selected.

For example, let's say that I have 4 slicer items (Ronny, Jonny, Lonny, Donny).

In the case of 2 selected items (Ronny, Lonny) I am looking for the following name on the worksheet tab (Ronny Lonny Pricing 05-24-2013).

In the case of 3 selected items (Ronny, Lonny, Donny) I am looking for the following name on the worksheet tab (Ron Lon Don Pricing 05-24-2013).

In the case of SlicerItems = True > 14 then I am looking for the following name on the worksheet tab (14 Selected Items Pricing 05-24-2013).

It would be nice to have some error handling asking the end-user if they want to overwrite an exact named worksheet.

Essentially – I have to have the phrase “Pricing MM-DD-YYYY” as the tail to every named worksheet. The rest is dependent on how many slicers were select and how much character room is left within the tab.

I really appreciate your help on this…

 

[Jerry Sullivan]

Thanks that helps clarify things.

Sheet names can only be a maximum of 31 characters so you might hit that limit often.
The code would also need to handle if there are special characters in the slicer item that are not allowed in sheet names.

Please clarify what you want to have on the added sheet. Is it just a blank sheet with the desired name, or should something be copied onto it?

 

[drmingle]

Thanks that helps clarify things.

Sheet names can only be a maximum of 31 characters so you might hit that limit often.
The code would also need to handle if there are special characters in the slicer item that are not allowed in sheet names.

Please clarify what you want to have on the added sheet. Is it just a blank sheet with the desired name, or should something be copied onto it?

It is a blank sheet.

 

[Jerry Sullivan]

Below is some code that you could try.

You'll need to rethink how to handle the 31 character sheetname limitation if you want to include multiple slicer items in the sheet name.

After subtracting the 19 characters needed for the fixed part " Pricing 05-24-2013", you only have 12 characters to work with.

That won't allow you to list anywhere near 13 you were wanting to list. In fact the string: "14 Selected Items Pricing 05-24-2013" is 36 characters, so even that will need to be shortened.

Sub SlicerName_Copy2()
    Dim slItem As SlicerItem
    Dim sSheetName As String, sDatePart As String
    Dim i As Long, iMaxchar As Integer, iReturn As Integer

    
    sDatePart = " Pricing " & Format(Date, "MM-DD-YYYY")
    iMaxchar = 31 - Len(sDatePart)

    
    With ActiveWorkbook.SlicerCaches("Slicer_PerID_NAME")
        For Each slItem In .VisibleSlicerItems
            sSheetName = sSheetName & " " & slItem.Name
        Next slItem
    End With

    
    sSheetName = Mid(sSheetName, 2, iMaxchar - 1) & sDatePart
    If SheetExists(sSheetName) Then
        iReturn = MsgBox(sSheetName & " already exists." & vbCr _
            & "Do you want to replace it?", vbYesNo, "Confirm Overwrite Sheet")
        If iReturn = vbNo Then Exit Sub
        Application.DisplayAlerts = False
        Sheets(sSheetName).Delete
        Application.DisplayAlerts = True
    End If

    
    Sheets.Add After:=Sheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = sSheetName
    On Error GoTo 0

    
    If ActiveSheet.Name <> sSheetName Then _
        MsgBox "Was unable to create Sheet: " & sSheetName
End Sub

 

[drmingle]

JS411,

Thank you for providing the code. However, I am running into an undefined section:

If SheetExists(sSheetName) Then

I decided to augment your code with the following:

Dim SheetExists As String

I also added this:

SheetExists = (Sheets(WorksheetName).Name <> "")

However, I am currently recieving a "expected array" error.

In any case, I still need a bit more help if you can spare the time.

 

[Jerry Sullivan]

Oops, I forgot to post the Function. Sorry.

Paste this into the same code module as the Sub()

Private Function SheetExists(sName As String) As Boolean
    On Error Resume Next
    SheetExists = Sheets(sName).Index > 0
End Function

 

[drmingle]

Magic, Magic...worked like a charm.

Thank you for the help!