How to copy cell contents AND formatting from one workbook to another?

[uk_dave&gill]

I have a workbook which I access and modify ("Staff Planner") which links to another workbook which others can only view ("Planner")

The contents of Planner change when Staff Planner is updated, through formulas like:-

='[Staff Planner.xls]Plan'!D1 (which sits in Cell D1 of Planner)

Problem is, the formatting doesn't copy. I need to copy the font colours from one workbook to the other, and the only way I can do this presently is by "Copy Format" tool.

Is there any way to do this automatically?

 

[Andrew Poulsom]

Sorry, no it can't be done automatically.

 

[uk_dave&gill]

Is it not possible with a macro?

 

[Andrew Poulsom]

Not without opening Staff Planner when Planner is opened.

 

[uk_dave&gill]

So, if they are both open it can be done?

This is interesting, as, to make sure that the updates happen to Planner as they should (as this is on a shared folder and "Staff Planner" is on my PC) I use Macro Scheduler to open Planner every day at 10:00. This way, all I have to do to ensure it's up to date is save it (as it's taking the info from Staff Planner even if it's closed)

Logically then, I could get Macro Scheduler to open both, and then have some VBA to do the copying?

 

[Andrew Poulsom]

Logically, yes you could. The macro would just need to Copy the UsedRange from sheet Plan in Staff Planner and Paste Special|Formats into A1 of the relevant sheet in Planner. You could use Planner's Workbook_Open event, checking that Staff Planner is open before running the rest of the code.

 

[uk_dave&gill]

Logically, yes you could. The macro would just need to Copy the UsedRange from sheet Plan in Staff Planner and Paste Special|Formats into A1 of the relevant sheet in Planner. You could use Planner's Workbook_Open event, checking that Staff Planner is open before running the rest of the code.

Can anyone help me with this? I must admit I'm hopeless with coding!!

 

[Andrew Poulsom]

You will never learn unless you have a go yourself. Use the macro recorder to provide some starter code.

You can use this function to check if a workbook is open:

http://www.j-walk.com/ss/excel/tips/tip54.htm#func6

 

[uk_dave&gill]

You will never learn unless you have a go yourself]

That's me told!

No, you're right of course. Trouble is I can't get my head round VBA, possibly because I spent a lot of time self-teaching myself on Superbase coding (and got quite good at it) and find it hard to "forget" one system while trying to learn another

Thanks for your help and advise

 

[uk_dave&gill]

Advice even.......