Macro to run regressions on multiple sheets

Hello,

I am wondering if there is a way to run regressions on multiple sheets rather than one each time.

I am attaching a ready sample file which has quite a few sheets. What I want to do is run a regression on all sheets.
The dependent variable (Y) is the second column whereas the independent (X) is the first one. The constant term should be zero.

Many thanks,

Costas

Ok, I have found the macro for the regression, made it work for all sheets. However the only problem that still remains is how to adjust the selection area in every sheet.
My code looks like this:

Please Login or Register  to view this content.

What I want to do is instead of cells B47 and C47 in every sheet, I want the macro to automatically pick the last used cell, since a sheet can have less or more than 47 observations.

Anyone could help?