Copy a range based on dynamic row numbers and paste into another workbook

Hi,

I'm looking to copy a range of 22 rows from columns A through to AZ on a different closed workbook and bring them into the active workbook and I'd like to be able to do it in VBA if possible. The closed workbook has at the moment about 10,000 rows. On the active workbook in cell B4 I have a data validation drop-down where the week number can be selected and then in D4 and E4 I have a couple of Vlookups that find the row numbers that need to be copied for the particular week number in B4. In my example file you can see an example of how I want to import the 22 rows. If I select a different week I would like the data over written in the same place.

I'd be very grateful for any help on this, I've managed to get the data in using Index Match formulas but because the files are located on a server to which the file paths are quite long the formulas look very messy. I enjoy working on formulas but my VBA is no where near the same level unfortunately.

Thanks in advance..

Hi tehneXus,

Thanks very much for taking the time to help me out, it works perfectly...you're a star !

Grizz.