Copy Used Range

B

billinr

I have a workbook with several sheets; I am trying to create a dynamic
summary page - one where the cell values are live links to the cells on the
other sheets.
The sheets I am copying have variable used ranges.
I can copy the used range (Thanks Ron DeBruin), but I am unable to have the
result show as "=sheetname!celladdress".

Any help is appreciated.

Thanks
 
G

Gary''s Student

If you copy data to the summary page, the copy will have to be repeated every
time the source change. Why not just use a link?:

=Sheet3!G10
 
B

billinr

Apologies - I didn't mention that I intend to re-run this sheet weekly in
order to capture any changes.
Otherwise, I would have just copied all and pasted as a link.

Thanks for replying.
 
G

Gary''s Student

Perhaps a very small macro:

Sub billinr()
Set r1 = Sheets("source").UsedRange
Set r2 = Sheets("destination").Range("A1")
r1.Copy r2
End Sub
 
B

billinr

This is sort of what I'm looking for, except that this does not allow for
looking at every sheet in the workbook.
How would I add that functionality, and have the output go to the next
available row?
Also, will the resultant cell values be dynamic links?

Thanks for the help.
 
Top