Extracting data from a range that changes daily based on a cells criteria

Hi, Everyday i receive an .xlsx file online.

Once downloaded I have to extract specific areas of data from the file to a particular worksheet in another workbook.

The source file has titles for ranges within the file which are constant but the rows of data beneath these titles vary on a daily basis.

I am looking for a macro that will extract a range of data below a particular title.

e.g. In the attached file (test sheet.xlsx) i want to extract A65-C122 into the worksheet 'new' in test sheet.xlsm (see attached).

The range I've specified changes daily but the text in the titles stay the same albeit moved to different rows, daily also.

I have spent the entire day trying to find a way using the Match function as recommended by 'nathansav', but my head is scrambled now at this stage.

Any help would be a huge load off.

Regards
John

match is not the right funtion for what you are trying to do.

Try this code:

Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("A:A").Select


    Selection.Find(What:= _
        "HP Renew Notebooks - Grade Silver (Grade A) 1 Year Warranty", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell.Value <> "HP Renew Notebooks - Grade Silver (Grade A) 1 Year Warranty" Then Exit Sub
Startrow = ActiveCell.Row + 1

    Selection.Find(What:= _
        "Windows 8 Grade A Refurbished Notebooks & Netbooks 1 Year Warranty", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell.Value <> "Windows 8 Grade A Refurbished Notebooks & Netbooks 1 Year Warranty" Then Exit Sub
Endrow = ActiveCell.Row - 1

    Range("A" & Startrow & ":C" & Endrow).Select

    Selection.Copy
    Sheets.Add After:=ActiveSheet

    ActiveSheet.Name = "New"
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub

Hi mehmetcik, Thanks for your response. This looks really close to what I need.

The thing is, I need to run this macro from worksheet 'new' in the 'test sheet.xlsm' workbook. So it will need go to workbook 'test sheet.xlsx', retrieve the data within the parameters you have set out and paste it back into 'test sheet.xlsm' workbook on the 'new' worksheet.

If this is this possible.

Thanks again

This is only a minor change.

Your code becomes:-

Note I have added a couple of lines to move any existing rows in sheet new down by the number of lines that you are copying.
This will stop it over writing your existing data. The lines are easily identified and deleted if you don't want them.

Sub Macro1()
'
' Macro1 Macro
'

'
    Windows("test sheet.xlsx").Activate
    
        Columns("A:A").Select


    Selection.Find(What:= _
        "HP Renew Notebooks - Grade Silver (Grade A) 1 Year Warranty", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell.Value <> "HP Renew Notebooks - Grade Silver (Grade A) 1 Year Warranty" Then Exit Sub
Startrow = ActiveCell.Row + 1

    Selection.Find(What:= _
        "Windows 8 Grade A Refurbished Notebooks & Netbooks 1 Year Warranty", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell.Value <> "Windows 8 Grade A Refurbished Notebooks & Netbooks 1 Year Warranty" Then Exit Sub
Endrow = ActiveCell.Row - 1

    Range("A" & Startrow & ":C" & Endrow).Select

    Selection.Copy

    Windows("test sheet.xlsm").Activate
    Sheets("new").Select
    
'Delete these rows if you want your data overwritten.  But if your existing data is longer that the new, you will end up with a mess.

    Rows("2:" & 1 + Endrow - Startrow).EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
End Sub

Hi mehmetcik, Is there a way to repeat the code to select additional data using the same formula but with different start and end rows, and then add it to the 'new' sheet at the first free blank cell in column A

Regards
John

Easily done.

Original range was A65-C122

What is the new range

Additionally i would like to include in the final results the section below row 132 'Windows 7 Grade A Refurbished Notebooks & Netbooks 1 Year Warranty' and above row 176 'Acer Vista / XP Notebooks – Refurbished 1 Year Warranty - Upgrade any notebook to Windows 7 for £29 + Vat'. So the range is A131-C175

Thx Mehmetcik