Hi Tinker

If there is a empty row/column between them you can use

ActiveCell.CurrentRegion

Do you know the first cell of each range ?

We need more info to help you
Bed time form me so it will be tomorrow after work before I can reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tinker" wrote in message ...
Ron,

I have a similar problem to Bovine's but with a slightly different twist. I
need to copy several ranges from multiple worksheets to a single page. The
difference is that the ranges, which is say 1 10x10 area each, vary in
number on each sheet and are seperated by blank spaces. Good thing is they
are always the exact same difference apart. I assume some sort of offset
function may work but have been able to get there yet.



"Ron de Bruin" wrote:

Read the page good

Copy the lastrow function also in the module

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bovine Jones" wrote in message
...
Ron

This looks quite promising but I get an error of a Sub or Function not
defined on lastrow. Have I done something wrong?

Thanks

BJ

"Ron de Bruin" wrote:

Hi Bovine

See this page
http://www.rondebruin.nl/copy2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bovine Jones" wrote in message
...
I've got a series of spreadsheets that I need to do some work on but in order
to do it I need to consolidate all of the data into a single worksheet first.

What I'm looking for is something like a macro that that will look at my
workbook, read all of the sheets and then append the data from all of the
sheets into a new single worksheet - presumably as the first sheet of the
workbook but that's not important.

The good news is that all of the sheets will have the same number of columns
(from A:U), but the bad news is that the number of sheets could vary from
workbook to workbook (which isn't the end of the world because I can make
slight modifications to the macro to take into account the number of sheets -
but ideally I'd just like to have one macro that reads all of the sheets and
requires no changing as I might not be maintaining things in the longer term)
and of course the really bad news is that the number of rows of data will
vary wildly and will change from run to run as new records are added and old
ones are removed.

Is that a tough ask? An easy ask? An impossible ask?

Any help would be absolutely fantastically received from this doddery old
Bovine that needs some assistance desperately!

Thanks.