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

Hi guys,
o)
I needed to retrieve data from a web page (Report names and dates) automatically using a Web Query in Excel. So far I have been able to retrieve the data (thanks to a macro that I found in this forum) using this code

Sub pullDataparaBoo()
x = Cells(Rows.Count, "T").End(xlUp).Row
For A = 1 To 17 Step 1
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.forrester.com/search?range=504001&N=10001 500003&tmtxt=       cloud, security&page=" & A & "&Count=2203", _
        Destination:=Range("$A$1"))
        .Name = "PullForrester Query"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Sheets.Add After:=ActiveSheet
    Next A
End Sub

After I run the macro I get the data in the following format and in multiple tabs:

For Infrastructure & Operations Professionals
Report:Huawei Takes On The Global Enterprise Market 
Regional Success Gives The Firm A Head Start, But The Global Enterprise Market Is A Whole New Ballgame
Bryan WangJuly 23, 2012
Huawei Technologies officially entered the global enterprise market in 2011, aiming to grow its market share by leveraging its experience in the carrier network equipment market and broadening its...

I need to create a table containing the fields Date and Report Name, in this case would be

Date |Report Name
July 23, 2012 | Huawei Takes on The Global Enterprise Market

The ideal scenario would be having all the projects and dates in a single table in one tab.

Please guys, I really need some help on this... I'm a beginner.

Thanks in advance and best regards,

Gabriel Valck

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

Thanks a lot Alf... I'll give it a try.

Thanks a lot Alf... I'll give it a try.