Linking Excel Worksheets

Hi,

I have 5 worksheets set up - A, B, C, D and E.

I would like to have worksheets B, C, D and E so that they are a copy of A.

Whenever there is a change made to worksheet A (Something is added, or
deleted or modified) then this is reflected on the other worksheets.

Is there anyway to link worksheets this way (not just particular cells)?

Hi

Click on tab A, hold down Shift and click on tab E
The sheets are now Grouped and at the top left of your screen you will see
[Grouped].
Now anything you do to any sheet will be reflected in all of the others.

Just click on any individual tab to Ungroup them.

Regards

Roger Govier


GLT wrote:
> Hi,
>
> I have 5 worksheets set up - A, B, C, D and E.
>
> I would like to have worksheets B, C, D and E so that they are a copy of A.
>
> Whenever there is a change made to worksheet A (Something is added, or
> deleted or modified) then this is reflected on the other worksheets.
>
> Is there anyway to link worksheets this way (not just particular cells)?

Hi Roger,

Thanks for your reply.

One thing a forgot to note was that the data in sheets B, C, D and E are
filtered.

When I use this method, the data typed into worksheet A appears on the other
sheets regardless of the filter.

Is there anyway that for the Data in worksheets B, C, D, E be obtained from
A, but the filters apply when the B, C, D and E are selected?

Cheers

"Roger Govier" wrote:

> Hi
>
> Click on tab A, hold down Shift and click on tab E
> The sheets are now Grouped and at the top left of your screen you will see
> [Grouped].
> Now anything you do to any sheet will be reflected in all of the others.
>
> Just click on any individual tab to Ungroup them.
>
> Regards
>
> Roger Govier
>
>
> GLT wrote:
> > Hi,
> >
> > I have 5 worksheets set up - A, B, C, D and E.
> >
> > I would like to have worksheets B, C, D and E so that they are a copy of A.
> >
> > Whenever there is a change made to worksheet A (Something is added, or
> > deleted or modified) then this is reflected on the other worksheets.
> >
> > Is there anyway to link worksheets this way (not just particular cells)?
>

If this is beyone Excel's scope to do this, I would appreciate someone saying
so...

"GLT" wrote:

> Hi Roger,
>
> Thanks for your reply.
>
> One thing a forgot to note was that the data in sheets B, C, D and E are
> filtered.
>
> When I use this method, the data typed into worksheet A appears on the other
> sheets regardless of the filter.
>
> Is there anyway that for the Data in worksheets B, C, D, E be obtained from
> A, but the filters apply when the B, C, D and E are selected?
>
> Cheers
>
> "Roger Govier" wrote:
>
> > Hi
> >
> > Click on tab A, hold down Shift and click on tab E
> > The sheets are now Grouped and at the top left of your screen you will see
> > [Grouped].
> > Now anything you do to any sheet will be reflected in all of the others.
> >
> > Just click on any individual tab to Ungroup them.
> >
> > Regards
> >
> > Roger Govier
> >
> >
> > GLT wrote:
> > > Hi,
> > >
> > > I have 5 worksheets set up - A, B, C, D and E.
> > >
> > > I would like to have worksheets B, C, D and E so that they are a copy of A.
> > >
> > > Whenever there is a change made to worksheet A (Something is added, or
> > > deleted or modified) then this is reflected on the other worksheets.
> > >
> > > Is there anyway to link worksheets this way (not just particular cells)?
> >

Hi

If you re-apply the filter on the sheet, by clicking the drop down and
re-selecting the original selection, this will "clean up" the sheet form any
extraneous values that get carried over to it.

If you wanted to automate this, put the following code into each of sheets
B,C,D,E

Private Sub Worksheet_Activate()

Selection.AutoFilter Field:=1, Criteria1:="Roger"

End Sub

Change the criteria on each one to the selection criteria you want.

Right click on the sheet tab>View Code and paste the code into the top right
pane.

Regards

Roger Govier


GLT wrote:
> If this is beyone Excel's scope to do this, I would appreciate someone saying
> so...
>
> "GLT" wrote:
>
>
>>Hi Roger,
>>
>>Thanks for your reply.
>>
>>One thing a forgot to note was that the data in sheets B, C, D and E are
>>filtered.
>>
>>When I use this method, the data typed into worksheet A appears on the other
>>sheets regardless of the filter.
>>
>>Is there anyway that for the Data in worksheets B, C, D, E be obtained from
>>A, but the filters apply when the B, C, D and E are selected?
>>
>>Cheers
>>
>>"Roger Govier" wrote:
>>
>>
>>>Hi
>>>
>>>Click on tab A, hold down Shift and click on tab E
>>>The sheets are now Grouped and at the top left of your screen you will see
>>>[Grouped].
>>>Now anything you do to any sheet will be reflected in all of the others.
>>>
>>>Just click on any individual tab to Ungroup them.
>>>
>>>Regards
>>>
>>>Roger Govier
>>>
>>>
>>>GLT wrote:
>>>
>>>>Hi,
>>>>
>>>>I have 5 worksheets set up - A, B, C, D and E.
>>>>
>>>>I would like to have worksheets B, C, D and E so that they are a copy of A.
>>>>
>>>>Whenever there is a change made to worksheet A (Something is added, or
>>>>deleted or modified) then this is reflected on the other worksheets.
>>>>
>>>>Is there anyway to link worksheets this way (not just particular cells)?
>>>

Hi Roger,

Thanks for your reply - this works except in sheets 2 & 4 I have some
columns hidden as the data displayed is not relevant for these sheets.

Can you advise if the feild number changes when there are hidden columns?

For example, column F (which is the 6th column) on the 1st worksheet (which
displays ALL data) is column F on the second worksheet but is actually the
second column.

I tried using:

Selection.AutoFilter Field:=2, Criteria1:="D"

Then the data disapears completely and I cant get it back even if I remove
the code(ie. I have to recreate the 2nd sheet from the 1st).

Also, is there a way i can get each worksheet to delete is data, copy it in
from worksheet a, then apply the filters?

When I update the data from worksheet 1, it does not carry accross onto the
other worksheets anymore.



"Roger Govier" wrote:

> Hi
>
> If you re-apply the filter on the sheet, by clicking the drop down and
> re-selecting the original selection, this will "clean up" the sheet form any
> extraneous values that get carried over to it.
>
> If you wanted to automate this, put the following code into each of sheets
> B,C,D,E
>
> Private Sub Worksheet_Activate()
>
> Selection.AutoFilter Field:=1, Criteria1:="Roger"
>
> End Sub
>
> Change the criteria on each one to the selection criteria you want.
>
> Right click on the sheet tab>View Code and paste the code into the top right
> pane.
>
> Regards
>
> Roger Govier
>
>
> GLT wrote:
> > If this is beyone Excel's scope to do this, I would appreciate someone saying
> > so...
> >
> > "GLT" wrote:
> >
> >
> >>Hi Roger,
> >>
> >>Thanks for your reply.
> >>
> >>One thing a forgot to note was that the data in sheets B, C, D and E are
> >>filtered.
> >>
> >>When I use this method, the data typed into worksheet A appears on the other
> >>sheets regardless of the filter.
> >>
> >>Is there anyway that for the Data in worksheets B, C, D, E be obtained from
> >>A, but the filters apply when the B, C, D and E are selected?
> >>
> >>Cheers
> >>
> >>"Roger Govier" wrote:
> >>
> >>
> >>>Hi
> >>>
> >>>Click on tab A, hold down Shift and click on tab E
> >>>The sheets are now Grouped and at the top left of your screen you will see
> >>>[Grouped].
> >>>Now anything you do to any sheet will be reflected in all of the others.
> >>>
> >>>Just click on any individual tab to Ungroup them.
> >>>
> >>>Regards
> >>>
> >>>Roger Govier
> >>>
> >>>
> >>>GLT wrote:
> >>>
> >>>>Hi,
> >>>>
> >>>>I have 5 worksheets set up - A, B, C, D and E.
> >>>>
> >>>>I would like to have worksheets B, C, D and E so that they are a copy of A.
> >>>>
> >>>>Whenever there is a change made to worksheet A (Something is added, or
> >>>>deleted or modified) then this is reflected on the other worksheets.
> >>>>
> >>>>Is there anyway to link worksheets this way (not just particular cells)?
> >>>
>

Hi

If you want to send me a copy of the workbook directly by email along with a
full explanation of what you want, I will see if I can sort it out for you.

Remove NOSPAM from my email address to send.

Regards

Roger Govier


GLT wrote:
> Hi Roger,
>
> Thanks for your reply - this works except in sheets 2 & 4 I have some
> columns hidden as the data displayed is not relevant for these sheets.
>
> Can you advise if the feild number changes when there are hidden columns?
>
> For example, column F (which is the 6th column) on the 1st worksheet (which
> displays ALL data) is column F on the second worksheet but is actually the
> second column.
>
> I tried using:
>
> Selection.AutoFilter Field:=2, Criteria1:="D"
>
> Then the data disapears completely and I cant get it back even if I remove
> the code(ie. I have to recreate the 2nd sheet from the 1st).
>
> Also, is there a way i can get each worksheet to delete is data, copy it in
> from worksheet a, then apply the filters?
>
> When I update the data from worksheet 1, it does not carry accross onto the
> other worksheets anymore.
>
>
>
> "Roger Govier" wrote:
>
>
>>Hi
>>
>>If you re-apply the filter on the sheet, by clicking the drop down and
>>re-selecting the original selection, this will "clean up" the sheet form any
>>extraneous values that get carried over to it.
>>
>>If you wanted to automate this, put the following code into each of sheets
>>B,C,D,E
>>
>>Private Sub Worksheet_Activate()
>>
>>Selection.AutoFilter Field:=1, Criteria1:="Roger"
>>
>>End Sub
>>
>>Change the criteria on each one to the selection criteria you want.
>>
>>Right click on the sheet tab>View Code and paste the code into the top right
>>pane.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>GLT wrote:
>>
>>>If this is beyone Excel's scope to do this, I would appreciate someone saying
>>>so...
>>>
>>>"GLT" wrote:
>>>
>>>
>>>
>>>>Hi Roger,
>>>>
>>>>Thanks for your reply.
>>>>
>>>>One thing a forgot to note was that the data in sheets B, C, D and E are
>>>>filtered.
>>>>
>>>>When I use this method, the data typed into worksheet A appears on the other
>>>>sheets regardless of the filter.
>>>>
>>>>Is there anyway that for the Data in worksheets B, C, D, E be obtained from
>>>>A, but the filters apply when the B, C, D and E are selected?
>>>>
>>>>Cheers
>>>>
>>>>"Roger Govier" wrote:
>>>>
>>>>
>>>>
>>>>>Hi
>>>>>
>>>>>Click on tab A, hold down Shift and click on tab E
>>>>>The sheets are now Grouped and at the top left of your screen you will see
>>>>>[Grouped].
>>>>>Now anything you do to any sheet will be reflected in all of the others.
>>>>>
>>>>>Just click on any individual tab to Ungroup them.
>>>>>
>>>>>Regards
>>>>>
>>>>>Roger Govier
>>>>>
>>>>>
>>>>>GLT wrote:
>>>>>
>>>>>
>>>>>>Hi,
>>>>>>
>>>>>>I have 5 worksheets set up - A, B, C, D and E.
>>>>>>
>>>>>>I would like to have worksheets B, C, D and E so that they are a copy of A.
>>>>>>
>>>>>>Whenever there is a change made to worksheet A (Something is added, or
>>>>>>deleted or modified) then this is reflected on the other worksheets.
>>>>>>
>>>>>>Is there anyway to link worksheets this way (not just particular cells)?
>>>>>