Hope someone can help with this!!

Here's roughly what I need to do - as an example, I have, essentially, 7
tables of daily information that begins as exactly the same data for each day
- i.e. each day's information is a copy of what is inputted for Monday, so
for the remainder of the week, I will be using, for example "=Monday!A1" for
each other day.

Now, what I need to be able to do is on Wednesday, for example, change
perhaps a few words of the information (at the moment, a copy of what is on
monday), but for this new change to be carried forward for the remainder of
the week.

This is a simplified version of what i need to do, but what I suppose I'm
asking is how I can progressively change information that only starts off as
a copy of text in an initial table of information.

There are two things to do here - change the text on Wednesday's
sheet, and then have this propagate through to the remainings sheets.

First of all, you can fix the value in the cell on Wednesday's sheet:
select the cell, click <copy, then right-click on the cell and choose
Paste Special | Values | OK then <Esc. Now you can amend the text in
that cell as you wish.

Then you need to click on Thursday's tab to select that sheet, and
then hold the CTRL key down while you also click on the tabs for
Friday, Saturday and Sunday - this will group them together so that
any change on one sheet will affect them all. Select the corresponding
cell that you changed for Wednesday and change the formula so that
instead of:

=Monday!A1

this gets changed to:

=Wednesday!A1

or whatever your cell reference is.

Then you can ungroup the sheets by clicking on the tab for Wednesday.

Hope this helps.

Pete

On Jan 28, 12:26*pm, Terry wrote:
Hope someone can help with this!!

Here's roughly what I need to do - as an example, I have, essentially, 7
tables of daily information that begins as exactly the same data for each day
- i.e. each day's information is a copy of what is inputted for Monday, so
for the remainder of the week, I will be using, for example "=Monday!A1" for
each other day.

Now, what I need to be able to do is on Wednesday, for example, change
perhaps a few words of the information (at the moment, a copy of what is on
monday), but for this new change to be carried forward for the remainder of
the week.

This is a simplified version of what i need to do, but what I suppose I'm
asking is how I can progressively change information that only starts off as
a copy of text in an initial table of information.

Thanks for taking the time to answer this Pete.

I think you might benefit from a little more explanation. If I were working
on a single workbook, this would be the way i'd do this. Basically, I work
for a college and have been asked to design a workbook for lecturers to
easily use to track individual students learning. They have continually, but
only slightly changing learning objectives, so their initial objectives (What
i called "Monday") may change by "Wednesday" and if they do, the edited
objectives need to be carried forward to subsequent "days".

I want to minimise the amount of copying / pasting that the lecturers have
to do - i.e they can just type in where the objectives have changed and this
will be reflected in their future objectives, all contained within one
workbook. I'm now thinking the best way to do this may be to write a
relatively straightforward macro which will copy all information from the
sheet they have edited and paste to all subsequent sheets, but not ones
before it. My only issue here is that I'm not sure of the coding to basically
say "copy this sheet and copy to all subsequent, regardless of their name". I
can do it if it copy and paste to a specifically named sheet, e.g somethig
like:
Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste

but am unsure of how to make this more general / flexible.

Apologies for the wordy response, but hope you have some suggestions /
recommendations to help me,

Thanks for your time.

"Pete_UK" wrote:

There are two things to do here - change the text on Wednesday's
sheet, and then have this propagate through to the remainings sheets.

First of all, you can fix the value in the cell on Wednesday's sheet:
select the cell, click <copy, then right-click on the cell and choose
Paste Special | Values | OK then <Esc. Now you can amend the text in
that cell as you wish.

Then you need to click on Thursday's tab to select that sheet, and
then hold the CTRL key down while you also click on the tabs for
Friday, Saturday and Sunday - this will group them together so that
any change on one sheet will affect them all. Select the corresponding
cell that you changed for Wednesday and change the formula so that
instead of:

=Monday!A1

this gets changed to:

=Wednesday!A1

or whatever your cell reference is.

Then you can ungroup the sheets by clicking on the tab for Wednesday.

Hope this helps.

Pete

On Jan 28, 12:26 pm, Terry wrote:
Hope someone can help with this!!

Here's roughly what I need to do - as an example, I have, essentially, 7
tables of daily information that begins as exactly the same data for each day
- i.e. each day's information is a copy of what is inputted for Monday, so
for the remainder of the week, I will be using, for example "=Monday!A1" for
each other day.

Now, what I need to be able to do is on Wednesday, for example, change
perhaps a few words of the information (at the moment, a copy of what is on
monday), but for this new change to be carried forward for the remainder of
the week.

This is a simplified version of what i need to do, but what I suppose I'm
asking is how I can progressively change information that only starts off as
a copy of text in an initial table of information.

Well, I don't envy you your task - I used to work in a college many
years ago !! <bg

I suppose you could have some event macro that detects if there is a
change to column A, and would then transfer that change to the
appropriate cell of the remaining sheets. If you then entered a loop
(For Each sht ... ) you can have appropriate If statements within that
loop to avoid pasting to "earlier" sheets. Using this approach you
wouldn't need to link the sheets back to earlier sheets - just have
straight copies of the first sheet for the initial "days".

Other people might add to this post here, or you might get further
advice if you post in the .programming group.

Be prepared, though - once you end up with something that works you
will have to support those lecturers with appropriate training (and
hand-holding) <vbg

Hope this helps.

Pete

On Jan 28, 2:09*pm, Terry wrote:
Thanks for taking the time to answer this Pete.

I think you might benefit from a little more explanation. If I were working
on a single workbook, this would be the way i'd do this. Basically, I work
for a college and have been asked to design a workbook for lecturers to
easily use to track individual students learning. They have continually, but
only slightly changing learning objectives, so their initial objectives (What
i called "Monday") may change by "Wednesday" and if they do, the edited
objectives need to be carried forward to subsequent "days".

I want to minimise the amount of copying / pasting that the lecturers have
to do - i.e they can just type in where the objectives have changed and this
will be reflected in their future objectives, all contained within one
workbook. I'm now thinking the best way to do this may be to write a
relatively straightforward macro which will copy all information from the
sheet they have edited and paste to all subsequent sheets, but not ones
before it. My only issue here is that I'm not sure of the coding to basically
say "copy this sheet and copy to all subsequent, regardless of their name". I
can do it if it copy and paste to a specifically named sheet, e.g somethig
like: *
* * Sheets("Mon").Select * *
* * Cells.Select
* * Selection.Copy
* * Sheets("Tues").Select
* * Cells.Select
* * ActiveSheet.Paste

but am unsure of how to make this more general / flexible.

Apologies for the wordy response, but hope you have some suggestions /
recommendations to help me,

Thanks for your time.



"Pete_UK" wrote:
There are two things to do here - change the text on Wednesday's
sheet, and then have this propagate through to the remainings sheets.

First of all, you can fix the value in the cell on Wednesday's sheet:
select the cell, click <copy, then right-click on the cell and choose
Paste Special | Values | OK then <Esc. Now you can amend the text in
that cell as you wish.

Then you need to click on Thursday's tab to select that sheet, and
then hold the CTRL key down while you also click on the tabs for
Friday, Saturday and Sunday - this will group them together so that
any change on one sheet will affect them all. Select the corresponding
cell that you changed for Wednesday and change the formula so that
instead of:

=Monday!A1

this gets changed to:

=Wednesday!A1

or whatever your cell reference is.

Then you can ungroup the sheets by clicking on the tab for Wednesday.

Hope this helps.

Pete

On Jan 28, 12:26 pm, Terry wrote:
Hope someone can help with this!!

Here's roughly what I need to do - as an example, I have, essentially, 7
tables of daily information that begins as exactly the same data for each day
- i.e. each day's information is a copy of what is inputted for Monday, so
for the remainder of the week, I will be using, for example "=Monday!A1" for
each other day.

Now, what I need to be able to do is on Wednesday, for example, change
perhaps a few words of the information (at the moment, a copy of what is on
monday), but for this new change to be carried forward for the remainder of
the week.

This is a simplified version of what i need to do, but what I suppose I'm
asking is how I can progressively change information that only starts off as
a copy of text in an initial table of information.- Hide quoted text -

- Show quoted text -

Thanks again for your time Pete,

The issue of too much hand-holding and instruction is precisely why I'm
hoping to make it as easy to use for them as possible! Hopefully the
"training" would pretty much be "change what you need to and then hit this
nice 'update' button or something" - I think not even they will struggle too
much with that!

Thanks again - I think i might put this in the programming section and see
what I get there.



"Pete_UK" wrote:

Well, I don't envy you your task - I used to work in a college many
years ago !! <bg

I suppose you could have some event macro that detects if there is a
change to column A, and would then transfer that change to the
appropriate cell of the remaining sheets. If you then entered a loop
(For Each sht ... ) you can have appropriate If statements within that
loop to avoid pasting to "earlier" sheets. Using this approach you
wouldn't need to link the sheets back to earlier sheets - just have
straight copies of the first sheet for the initial "days".

Other people might add to this post here, or you might get further
advice if you post in the .programming group.

Be prepared, though - once you end up with something that works you
will have to support those lecturers with appropriate training (and
hand-holding) <vbg

Hope this helps.

Pete

On Jan 28, 2:09 pm, Terry wrote:
Thanks for taking the time to answer this Pete.

I think you might benefit from a little more explanation. If I were working
on a single workbook, this would be the way i'd do this. Basically, I work
for a college and have been asked to design a workbook for lecturers to
easily use to track individual students learning. They have continually, but
only slightly changing learning objectives, so their initial objectives (What
i called "Monday") may change by "Wednesday" and if they do, the edited
objectives need to be carried forward to subsequent "days".

I want to minimise the amount of copying / pasting that the lecturers have
to do - i.e they can just type in where the objectives have changed and this
will be reflected in their future objectives, all contained within one
workbook. I'm now thinking the best way to do this may be to write a
relatively straightforward macro which will copy all information from the
sheet they have edited and paste to all subsequent sheets, but not ones
before it. My only issue here is that I'm not sure of the coding to basically
say "copy this sheet and copy to all subsequent, regardless of their name". I
can do it if it copy and paste to a specifically named sheet, e.g somethig
like:
Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste

but am unsure of how to make this more general / flexible.

Apologies for the wordy response, but hope you have some suggestions /
recommendations to help me,

Thanks for your time.



"Pete_UK" wrote:
There are two things to do here - change the text on Wednesday's
sheet, and then have this propagate through to the remainings sheets.

First of all, you can fix the value in the cell on Wednesday's sheet:
select the cell, click <copy, then right-click on the cell and choose
Paste Special | Values | OK then <Esc. Now you can amend the text in
that cell as you wish.

Then you need to click on Thursday's tab to select that sheet, and
then hold the CTRL key down while you also click on the tabs for
Friday, Saturday and Sunday - this will group them together so that
any change on one sheet will affect them all. Select the corresponding
cell that you changed for Wednesday and change the formula so that
instead of:

=Monday!A1

this gets changed to:

=Wednesday!A1

or whatever your cell reference is.

Then you can ungroup the sheets by clicking on the tab for Wednesday.

Hope this helps.

Pete

On Jan 28, 12:26 pm, Terry wrote:
Hope someone can help with this!!

Here's roughly what I need to do - as an example, I have, essentially, 7
tables of daily information that begins as exactly the same data for each day
- i.e. each day's information is a copy of what is inputted for Monday, so
for the remainder of the week, I will be using, for example "=Monday!A1" for
each other day.

Now, what I need to be able to do is on Wednesday, for example, change
perhaps a few words of the information (at the moment, a copy of what is on
monday), but for this new change to be carried forward for the remainder of
the week.

This is a simplified version of what i need to do, but what I suppose I'm
asking is how I can progressively change information that only starts off as
a copy of text in an initial table of information.- Hide quoted text -

- Show quoted text -

You're welcome, Terry - good luck!!

Pete

On Jan 28, 3:19*pm, Terry wrote:
Thanks again for your time Pete,

The issue of too much hand-holding and instruction is precisely why I'm
hoping to make it as easy to use for them as possible! Hopefully the
"training" would pretty much be "change what you need to and then hit this
nice 'update' button or something" - I think not even they will struggle too
much with that!

Thanks again - I think i might put this in the programming section and see
what I get there.