cell reference from one sheet to another

[SeanJ]

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.


[VBA]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[/VBA]

[Bob Phillips]

[vba]

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
[/vba]

[SeanJ]

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?

[VBA].Cells(LastUsedCell + 2, 1).Paste [/VBA]

[Bob Phillips]

That will teach me to make last minute changes

[vba]

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
[/vba]

[SeanJ]

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]

Shown where? As it is copied from MAster that seems superfluous to me.

[SeanJ]

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]

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]

Sorry but I was think on the line of Paste Link.