Macro to select data based in a search term and populate a table

Not sure of how you whish to sort your data. I've tested the "Web Query" macro and got 18 sheets.

Only the first sheet contains a string starting with "Report:Huawei" so my macro loops through all the sheets and delete the one that does not contain this string.

It will only save the 1st sheet even if the string "Report:Huawei" is not found as you can not delete all sheets in a workbook it must contain at least one sheet.

Sub TrimSheet()

Dim i As Integer
Dim FoundRange As Range

Application.DisplayAlerts = False

For i = Sheets.Count To 2 Step -1

Sheets(i).Activate

Range("A1").Activate

Set FoundRange = Cells.Find(What:="Report:Huawei", LookIn:=xlFormulas, LookAt:=xlPart)
     
    If FoundRange Is Nothing Then
       Sheets(i).Delete
    End If
Next i

Application.DisplayAlerts = True

End Sub

You now need to show how the and what part of the data should be extracted as there are more than 500 hundred lines in sheet1. I wouls suggest upload a sample file with both "raw" data and "structured" data.

Alf