Excel macro to summarize SEO data exports

srContentGapPivot.vb

Sub semrushContentGapPivot()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As pivottable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sheet 1")

 LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PivotTable")

Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")


    Sheets("PivotTable").Select
    
Sheets("PivotTable").Select
    
    With ActiveSheet.PivotTables("PivotTable").PivotFields("URL")
        .Orientation = xlRowField
        .Position = 1

   End With
    With ActiveSheet.PivotTables("PivotTable").PivotFields("SEO score")
        .Orientation = xlDataField
        
    End With

    With ActiveSheet.PivotTables("PivotTable").PivotFields("URL")
        .AutoSort Order:=xlDescending, Field:="Sum of SEO score"
    
    End With

End Sub