Help needed to pick up data from multiple excel files using vba

[rahiljaved]

I have various excel files that I recieved every week with data in it. I need a macro to pick data from each of those files from cell B9 to the end where data ends (its different in all files) and paste it in target file one over the other. Example in file 1 data starts from B9 - G59 so it copy and paste in target file from A2 - F52 then pick data from file 2 data starts from B9 - G69 and paste in target file from A53 - F102 and so on so forth. The target file I have set some formula in pivot table so that when the data moves in it automatically picks up and from column H - N formula will work using data that is copied from these files.

Background: I have these files coming in email so I write a macro in outlook to save all the attachments automatically one click in a folder. Now the second step is to merge all those files in one but only specific cells which has data in it becuase from A1 - B8 there are headers and other unnecessary data which i dont need in target file so that is why i need it from B9 - the end of the data range. Please help thanks in advance.

 

[Crystalyzer]

do you want this to open ALL files in that location to extract the data or will there be some criteria that will only open the most recent files?