Converting a range to a table and then creating a pivot table through a series of WBs

[Tanner_2004]

Hi All:

I have tried to solve this on my old without much success, I did get as far as this. However, I am looking to do this:

I have this code that loops through all of the workbooks in a folder (as you can see). I then want to change the range that starts in Sheet1, cell A1, to a Table.
From that point, I want to create a basic pivot table from the Table that was just created, and inset the pivot table on a new worksheet. Once this is procedure is run, I want to close the workbook, save the changes and do the sane for the next workbook in the designated folder. The range will always be in Sheet1 and start in A1, however, the range will change in width (columns) and length/height (rows).

Sub Rename_Sheet1()
    Dim strPathh As String, strFile As String, counter As Long
   
    strPath = "C:\Users\lru8079\Desktop\test\"
    strFile = Dir(strPath & "*.xlsx")
   
    Application.ScreenUpdating = False
    Do While strFile <> ""
        With Workbooks.Open(strPath & strFile)
            .Sheets(1).Name = "Sheet1"
            .Close SaveChanges:=True
        End With
        counter = counter + 1
        strFile = Dir
    Loop
    Application.ScreenUpdating = True
   
    MsgBox counter & " files altered. ", vbInformation, "Process Complete"
   
End Sub

Thank you for reading and any advice.