Macro extracting data from various worksheets

Hi All,

I am having great difficulities writing a macro that could extract data from various worksheets within a specified folder.

The following is what I would like to do:

1) Have a main workbook which extracts data from specific sheets of another workbook into various worksheets within the main workbook. Ideally incorporate an OFFSET type function to read last data from these other worksheets.
2) Within the main worksheet, have a cell say "A1" where the file name is typed without the full path. The reason for this is because my file names will change periodically.
3) Expanding on point 1, an example would be to have data from Workbook 2 sheet 1 copied into Workbook 1 sheet 1, also Workbook 2 sheet 2 copied into Workbook 1 sheet 2 etc. The data would always start from a specific cell example cell "B2" in Workbook 2.

Please help, any assistance would be very much appreciated.

Regards

Laylow