OK, I have recorded a macro. The problem is I don't know how to do the append
part, so when it runs it just keeps recording to the same static cell that I
originally selected.

I'll answer some of the questions you've posed and also include the code of
the macro that doesn't quite work (does everything but append!)

What event will trigger the transfer form one book to the other (button click?,
spreadsheet close?, cell value change?).
Cell value change. But it's changed already in a previous macro so its just
ready to be copied.

Is it always the same 5 cells to be copied.
Yes

What are the book names and what are the sheet names.
You can see it in the macro code I 've pasted below

Is the destination book going to be open already or does the code need to open the book.
Needs to open the book, but that part of the macro already works.

Should the book be closed when the code finishes.
Not needed.

Macro code:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/22/2005 by tvshoj
'

'
ChDir "R:\Production\SAP\Inventory Reports"
Workbooks.Open Filename:= _
"R:\Production\SAP\Inventory Reports\Inventory Levels - Actual by
Day.xls"
Sheets("Count").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C6").Select
Windows("Warehouse Macro Template.xls").Activate
Sheets("Summary").Select
Range("C8:C13").Select
Selection.Copy
Windows("Inventory Levels - Actual by Day.xls").Activate
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Sheets("Weight").Select
Range("B6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C6").Select
Windows("Warehouse Macro Template.xls").Activate
Range("F8:F13").Select
Selection.Copy
Windows("Inventory Levels - Actual by Day.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Range("B7").Select
ActiveWorkbook.Save
Windows("Warehouse Macro Template.xls").Activate
Range("B16").Select
End Sub