PDA

View Full Version : cell reference from one sheet to another



SeanJ
08-25-2008, 06:41 AM
Hello guys,

I am trying to do the following:

I have a sheet called Master. Everytime a new sheet is added I need to select "A1:Q9" from the new sheet and display it on the master sheet. Problem is if changes are done on a existing sheet the changes must reflect on the Master sheet. ALso the formatting must copy over from the new sheets to the Master sheet. I have started code below but I am stump. with how to proceed.


ws.Range("A1:Q9").Select
With Selection
.Copy
End With

Set ws = wb.Sheets("Master")
With ws
.Select
LastUsedCell = .Cells(.Rows.Count, "b").End(xlUp).Row
newcellloc = LastUsedCell + 2
End With
ws.Cells(newcellloc, 1).Select
ActiveSheet.Paste

Bob Phillips
08-25-2008, 06:48 AM
Set Masterws = wb.Sheets("Master")
With Masterws
LastUsedCell = .Cells(.Rows.Count, "b").End(xlUp).Row
ws.Range("A1:Q9").Copy .Cells(LastUsedCell + 2, 1).Paste
End With

SeanJ
08-25-2008, 07:01 AM
xld it errors with an object on this part of the code. After it copy the information over to the master sheet how can the updated information be shown on the master sheet?

.Cells(LastUsedCell + 2, 1).Paste

Bob Phillips
08-25-2008, 07:10 AM
That will teach me to make last minute changes



Set Masterws = wb.Sheets("Master")
With Masterws
LastUsedCell = .Cells(.Rows.Count, "b").End(xlUp).Row
ws.Range("A1:Q9").Copy .Cells(LastUsedCell + 2, 1)
End With

SeanJ
08-25-2008, 07:37 AM
That work great. I got one more question. After it copy the information over to the master sheet how can the updated information be shown on the master sheet?

Or do I got to start a new thread?

Bob Phillips
08-25-2008, 10:01 AM
Shown where? As it is copied from MAster that seems superfluous to me.

SeanJ
08-25-2008, 10:14 AM
Sorry for not being clear.

Lets say that sheet1 has data points between the range "a1:q9" and if I update and of those data points I want to reflect them on the master sheet.

Anytine I add a new sheet I may need to change a data point to the set range and reflect it on the master sheet.

Bob Phillips
08-25-2008, 10:21 AM
But they won't be A1:Q9 will they, they will be somewhere down sheet1 because we copied to the first free row. That is the difficulty, we would normally use event code, but we can't trap the range being updated, we wouldn't know where it is. We could name the target range I suppose.

SeanJ
08-25-2008, 10:36 AM
Sorry but I was think on the line of Paste Link.