copy ranges from multiple worksheets

I have a workbook # 511 with 7 sheets. I want to copy the first 6
sheets to sheet 7 from these specific ranges.


sheets("Sheet1").Select
Range("A5:Q58")

Sheets("Sheet 2").Select
Range("A5:Q58")

Sheets("Sheet 3 ").Select
Range("A5:Q38")

Sheets("Sheet 4 ").Select
Range("A5:Q25")

Sheets("Sheet 5 ").Select
Range("A5:Q35")

Sheets("Sheet 6 ").Select
Range("A5:Q25")


3 questions;

Exactly how do I do this so that changes made to any sheet is also
reflected on Sheet 7 (TOTALS_PAGE)

How can I make the current column D for instance in the 6 workbooks be
based on a past workbook from last weeks's cloumn P ( NOT d ) for
instance.


Each workbook is named with a number based on the last number used.
This is 511 last week was 510 etc. How do I have either VBA or a
macro automatically look and use that number to find the last
workbook. Cell C 1 always contain the name/number of the current
workbook on each worksheet.

Hi

What do you want to do with returned data? I.e. do you want to display data
from Sheet1 p.e. in range A5:Q58, data from Sheet2 in range A59:Q116, etc.?
Or do yo want to calculate sum or count or average of values p.e. cells A5
from 6 sheet, etc.

To simply return a value from another sheet, you can use link. P.e.
=Sheet1!A5
rerturns the value from cell A5 on sheet Sheet1. To avoid empty cells
returned as 0's, you can modify this formula slightly:
=IF(Sheet1!A5="","",Sheet1!A5)
(Combining absolute and relative references - Sheet1!A5; Sheet1!$A5;
Sheet1!A$5; Sheet1!$A$5 - you can control how cell references in link
formula will behave when the formula is copied to some range)


When yo want to return some aggregate value, based on values on all 6 sheet,
you can include links in aggregate functions. P.e.
=SUM(Sheet1!A5,Sheet2!A5,Sheet3!A5,Sheet4!A5,Sheet5!A5,Sheet6!A5)
or
=IF(SUM(Sheet1!A5,Sheet2!A5,Sheet3!A5,Sheet4!A5,Sheet5!A5,Sheet6!A5)=0,"",SU
M(Sheet1!A5,Sheet2!A5,Sheet3!A5,Sheet4!A5,Sheet5!A5,Sheet6!A5))
does return the sum of values in cell A5 on sheets Sheet1...Sheet6.
There is a way to shorten the formulas above:
=SUM(Sheet1:Sheet6!A5)
or
=IF(SUM(Sheet1:Sheet6!A5)=0,"",SUM(Sheet1:Sheet6!A5))
, but you have then to ensure, that no sheet except ones you want to sum is
placed between Sheet1 and Sheet6, and that no sheet you want to sum is moved
outside from sheet range marked with Sheet1 and Sheet6.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"simora" <[email protected]> wrote in message
news:[email protected]...
>
> I have a workbook # 511 with 7 sheets. I want to copy the first 6
> sheets to sheet 7 from these specific ranges.
>
>
> sheets("Sheet1").Select
> Range("A5:Q58")
>
> Sheets("Sheet 2").Select
> Range("A5:Q58")
>
> Sheets("Sheet 3 ").Select
> Range("A5:Q38")
>
> Sheets("Sheet 4 ").Select
> Range("A5:Q25")
>
> Sheets("Sheet 5 ").Select
> Range("A5:Q35")
>
> Sheets("Sheet 6 ").Select
> Range("A5:Q25")
>
>
> 3 questions;
>
> Exactly how do I do this so that changes made to any sheet is also
> reflected on Sheet 7 (TOTALS_PAGE)
>
> How can I make the current column D for instance in the 6 workbooks be
> based on a past workbook from last weeks's cloumn P ( NOT d ) for
> instance.
>
>
> Each workbook is named with a number based on the last number used.
> This is 511 last week was 510 etc. How do I have either VBA or a
> macro automatically look and use that number to find the last
> workbook. Cell C 1 always contain the name/number of the current
> workbook on each worksheet.
>

Arvi Laanemets wrote:
> Hi
>
> What do you want to do with returned data? I.e. do you want to display data
> from Sheet1 p.e. in range A5:Q58, data from Sheet2 in range A59:Q116, etc.?
> Or do yo want to calculate sum or count or average of values p.e. cells A5
> from 6 sheet, etc.

*** I just want to copy the data and retain the links, so that changes
made in any of the original sheet will be reflected on the last sheet.


>
> To simply return a value from another sheet, you can use link. P.e.
> =Sheet1!A5
> rerturns the value from cell A5 on sheet Sheet1. To avoid empty cells
> returned as 0's, you can modify this formula slightly:
> =IF(Sheet1!A5="","",Sheet1!A5)
> (Combining absolute and relative references - Sheet1!A5; Sheet1!$A5;
> Sheet1!A$5; Sheet1!$A$5 - you can control how cell references in link
> formula will behave when the formula is copied to some range)
>
You lost me somewhere in there. I think I simply need a copy &
pastelink that goes out to those shetts and selects those rows and does
the copying, then pastes them on the final sheet.
>
> When yo want to return some aggregate value, based on values on all 6 sheet,
> you can include links in aggregate functions. P.e.
> =SUM(Sheet1!A5,Sheet2!A5,Sheet3!A5,Sheet4!A5,Sheet5!A5,Sheet6!A5)
> or
> =IF(SUM(Sheet1!A5,Sheet2!A5,Sheet3!A5,Sheet4!A5,Sheet5!A5,Sheet6!A5)=0,"",SU
> M(Sheet1!A5,Sheet2!A5,Sheet3!A5,Sheet4!A5,Sheet5!A5,Sheet6!A5))
> does return the sum of values in cell A5 on sheets Sheet1...Sheet6.
> There is a way to shorten the formulas above:
> =SUM(Sheet1:Sheet6!A5)
> or
> =IF(SUM(Sheet1:Sheet6!A5)=0,"",SUM(Sheet1:Sheet6!A5))
> , but you have then to ensure, that no sheet except ones you want to sum is
> placed between Sheet1 and Sheet6, and that no sheet you want to sum is moved
> outside from sheet range marked with Sheet1 and Sheet6.
>
No other sheets will be inserted or moved.

.................
How can I make the current column D for instance in the 6 workbooks be
based on a past workbook from last weeks's cloumn P ( NOT d ) for
instance.

Each workbook is named with a number based on the last number used.
This is 511 last week was 510 etc. How do I have either VBA or a
macro automatically look and use that number to find the last
workbook. Cell C 1 always contain the name/number of the current
workbook on each worksheet.