Finding a range for Copying & Pasting

[John R]

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

 

[Tom Ogilvy]

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")