Copy an entire range and make it uneditable

[Gavin Ling]

I'm not sure if this is going to work but:

I have a company schedule on Worksheet 1. It has days, dates, months, jobs etc. in Rows 1-5. There is a variety of formatting present - for example, weekends are filled a certain colour, as are certain jobs.

What I want to do is to have these five or so rows copied onto several other worksheets, including their formats, but make them uneditable.

So if you want to change a job, you can do it on worksheet 1 and can't do it on worksheet 3. But when you change a job on Worksheet 1, it also changes on worksheet 3.

Does that make sense?

[Fotis1991]

Give a try to this.

Please Login or Register  to view this content.

[Gavin Ling]

Hi,

Thanks. It works, but I need it to do it constantly - so changes in "Sheet 3" are made 'live'. So rather than copying and pasting "Sheet 1 A1" into "Sheet 3 A1" on demand, I guess I simply want "Sheet 3 A1" to just always replicate "Sheet 1 A1".

I know I could do it with a basic in-cell formula (='Sheet 1'!A1) but the moment anyone copies and pastes anything on Sheet 1 in messes up all the maths. So I figured if there was a way of simply saying that the whole of Row 1 on Sheet 3 should always be a duplicate of Row 1 on Sheet 1 that would solve my problem.

Am I making sense? Sorry if it wasm't/isn't clear!

G

[Fotis1991]

Then this should works.

Please Login or Register  to view this content.

[Gavin Ling]

Hi,

I'm struggling to make it do what I'm after. It might be partly to do with not understanding completely where the VBA should go.

See attached basic example. I put the code in WS3Schedule Macro Test 1.xls

[Fotis1991]

We need a new module for this. User has to click a button in sheet1 every time that wants the sheet 3 to be updating..

Please Login or Register  to view this content.

[Gavin Ling]

Hi,

Thanks, that works.

Is it possible to make the Macro run at the point at which I activate Sheet3 rather than the button? So it the copy/paste process works at the point at which I swap from Sheet1 to Sheet3. Is that possible?

G

[Fotis1991]

In sheet3 private module, this usually works..but not now...Sorry!

Please Login or Register  to view this content.

[john55]

or
in worksheet3, view code, worksheet-activate copy paste it

Please Login or Register  to view this content.

[Gavin Ling]

It does if I change 'try' to this:

Please Login or Register  to view this content.

I've taken out the various protection bits to help me understand what's going on. I'll now figure out how to put them back in :-)

Thanks for all your help. I hoped there was an easy way of simply saying "Sheet3 A1 = Sheet1 A1" but it seems not!

Many thanks

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also,, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Gavin Ling]

One more thing! Sorry if this should be in a new post now I've marked it 'solved'....

In row 1 (starting at B1) are dates in Excel format. When the copy/paste function is complete I want to jump to the cell with today's date in it.

I found a couple of posts on here doing a similar thing but I've failed to make the two things happen together (mostly because I'm not very good at this!).

G

[Fotis1991]

Perhaps you could use Time stamp for this. or to record a macro to replace the row 1 dates in sheet3, after you run the previous macro.

[Gavin Ling]

I found some code on the forum and managed to alter it (though a series of trial and error...) to amke it work:

Please Login or Register  to view this content.

...then I put a 'call SelectDate' command after the 'try' module.

Hurrah. Thanks to both of you. Have a good weekend