Hi all,

I hope you can help!

I have a excel spreadsheet which on numerous occasions is
will have varying amounts of rows and columns. Is it
possible to always find the extent of a range for copying
and pasting to another worksheet even if the original
range has changed by adding / and or deleting rows.

Now the range is made up of mainly formula and has colour
etc. Now I know the macro recorder will facilitate copying
and pasting on a set range, but I need to build in
functionality to tell excel the new extent of an altered
range and then include all formats etc, but not formula on
the new sheet.

Regards,

John

assuming there are no completely blank rows or columns in your range -
further assume the top left populated cell is A1

Range("A1").CurrentRegion.Copy Destination:= _
Activesheet.Next.Range("B34")

--
Regards,
Tom Ogilvy


"John R" wrote in message
...

Hi all,

I hope you can help!

I have a excel spreadsheet which on numerous occasions is
will have varying amounts of rows and columns. Is it
possible to always find the extent of a range for copying
and pasting to another worksheet even if the original
range has changed by adding / and or deleting rows.

Now the range is made up of mainly formula and has colour
etc. Now I know the macro recorder will facilitate copying
and pasting on a set range, but I need to build in
functionality to tell excel the new extent of an altered
range and then include all formats etc, but not formula on
the new sheet.

Regards,

John