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?

 

[uk_dave&gill]

OK, I really have had a go at this, using VBA from other workbooks I have and LOTS of Googling!

Getting a bit stumped now though.

Decided I didn't want the whole of every sheet copied from one workbook to another (after I'd found a way to do this!) and that I'd rather do a range instead. Seems easy enough to copy from somewhere in the sheet to somewhere else, ie

Sub RangerCopy()
With ActiveSheet
.Range("A1:I19").Copy Destination:=Destination.Range("D23")

End With
End Sub

But I can't work out how to make another workbook the destination!

What I need is to copy the range A315:I1500 from "Staff Planner" to A1 in "Planner". Generally both workbooks will be open although if I could also get it to open them, do the copy, save both and close them, this would be a bonus!