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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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