instead of
worksheets("Sheet1").Range("A1:Z200")

use

Worksheets("Sheet1").Range("A1").CurrentRegion

--
Regards,
Tom Ogilvy

"Éidhne in Ireland" wrote in message
...
Hi,

I'm trying to create a pivot table from an external text
file using a macro. The data always has the same number
of colums (12), but the number of rows varies. This is a
problem - when I record the original macro the data range
for the pivot table is stored in it. When I try to run the
same macro on a data set with a larger number of rows, all
data after the final row indicated by the original data
set is ignored.

Any suggestions greatly appreciated.

Thanks in advance......