combining worksheets and lookups
 

I have a workbook # 511 with 7 sheets. I want to copy the following 6
sheets to sheet 7


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 be based on a past
workbook from last weeks's cloumn P ( NOT d )


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.