Summarize data from one workbook in another

I find myself out of my depth as I have never worked with more than one workbook before coupled with the fact that I am gathering data using variables in cell and range references.

Basically I have two workbooks Book1 which will display the output and contains the code and Book2 which which is a long list of sales data in a CSV file.

I have managed to open this and sort it by person and reverse date order. What I want to do is gather the data and produce an average of the last 30 sales of each individiual from Book2 and display it in Book 1.

I have no problem ascertaining the start position of each sales person and therefore calculating the cell references required, just averaging it using variables and displaying it in Book1.

What I am having trouble with is defining ranges and making calculations in another workbook and displaying the results in Book1

All help, simple examples and principles very welcome.

I have read various other threads but still keep getting hung up on this principle so I think I need someone to give me an beginners guide. The sales.csv is just columns of names, sales and dates, I just want to average the last so many days of sales and I can calculate the range for those.

My problem comes when trying to average a range in another workbook using variables in the range, I just don't seem to be understanding the principle and that's before I start using variables in the range.

My code starts as follows.

Please Login or Register  to view this content.

I tried this from building a macro in Excel - didn't work

Please Login or Register  to view this content.

I build this one up myself and of course didn't work.

Please Login or Register  to view this content.

And lastly I tried this still with problems

Please Login or Register  to view this content.