Good afternoon, I have a Pivot Tabl

[PWD]

Good afternoon, I have a Pivot Table that is refreshed from a weekly set of data. One of the row fields is the Cancer Site, as below.

Breast
Colorectal
Gynaecology
Haematology
Head and Neck
Lung
Skin
Upper GI
Urology

I have added a button (both "Text Box" allocated to a macro, which works & "ActiveX Text Box" for testing this) for each site and have just put together some code to pull out the pivot data into a separate worksheet, as below.

Sub Colorectal_Test()
    Dim worksh As Integer
    
    worksh = Application.Sheets.Count
    
        For x = 1 To worksh 'Cycle through the worksheets
            If Worksheets(x).Name = Sheets("PIVOT").Range("B4") Then 'Check to see if the worksheet already exists
              Sheets(Worksheets(x).Name).Activate
               End
            End If
        Next x
'Create worksheet
    Sheets("PIVOT").Range("C4").ShowDetail = True
    ActiveSheet.Name = Sheets("PIVOT").Range("B4") 'Name it according to the site
    ActiveSheet.Range("A1").Select

End Sub

There may be occasions where one of the cancer sites doesn't appear in the pivot table as there are no recorded patients that week. Next week there may be no Haematology patients and this week there were no Paediatric, but next week there may be some. What I would like to do - and believe me my small brain has been trying - is to allocate the text name of each button based on the row labels in column B and then also run the appropriate macro based on that. Am I daft?

Many thanks,
D€$

 

[PWD]

OK, I've got the allocation of the names from cells in Column "B" to a standard TextBox. And I can apply some logic to extract the correct data.

Many thanks,
D€$