Would something like this work for you?

Sub Demo()
With Sheets("Sheet1").Range("A1").CurrentRegion
.Copy
.PasteSpecial xlPasteValues
.EntireColumn.AutoFit
.Name = "RangeForPivot"
End With
Application.CutCopyMode = Range("A2").Select
End Sub

--
Dana DeLouis
Win XP & Office 2003


"Ant" wrote in message
...
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Cells.Select
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End With