VBA -- Creating Pivot Table from datamodel table

Dear all,

I have data, ticket details in sheet " Backlog " which had duplicate entries. I need to prepare a summary in sheet " Backlog_Summary " for unique ticket no. entries.

Manually, I select the entire table and create a pivot by adding it the data to a data model. This will enable to create summary by using Distinct Count. { This is my goal }

I have the following VBA Code which I had modified based on macro recording when I did it manually. However, the following code doesn't do anything:

  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveWorkbook.Connections("WorksheetConnection_Backlog!" & srange), Version:=6). _
    CreatePivotTable TableDestination:="Backlog_Summary!R1C1", TableName:="BacklogSummaryPivotTable", DefaultVersion:=6

I've attached the macro file which has the whole code.

Could you please help me understand the mistake and correct the code?

Appreciate any help on the same.

Thanks,
Vinod Krishna