Alistair,

To answer your original question, No, you can't change the size of a
worksheet. However, there is a solution to your problem.

Create a dynamic name for your pivot table Data Source using the
'Offset' formula. The formula in your 'Refers To:' field should look
something like this...

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

where...
Sheet1 is the name of the sheet containing the data
$A$1 is the top left of your data
$A:$A contains no blank cells within the data list and no populated
cells outside of the data list

Refer to online Help for a full explanation of the OFFSET Worksheet
function

HTH,
Nick.