You've got a couple of problems:

You can only select a cell on a selected sheet.

With Sheets("Sheet1")
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Application.CutCopyMode = False
.Range("A1", .Range("AA" & .Rows.Count).End(xlUp)).Name = "RangeForPivot"
.select '<-- added
.Range("A2").Select
.Cells.EntireColumn.AutoFit
End With

and this line:
..Range("A1", Range("AA" & Rows.Count).End(xlUp)).Name = "RangeForPivot"
has an unqualifed range object (Range("aa" & ...

This Range("aa"... refers to the activesheet--not always Sheet1.

So I added a couple of dots:
..Range("A1", .Range("AA" & .Rows.Count).End(xlUp)).Name = "RangeForPivot"



Ant wrote:

Dave - that works much better now without using Selection. I have added some
additional code for a Range which debugs if I happen to be in any Sheet other
than Sheet1. Any ideas how I can make this work if I am not in Sheet1?

With Sheets("Sheet1")
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Application.CutCopyMode = False
.Range("A1", Range("AA" & Rows.Count).End(xlUp)).Name = "RangeForPivot"
(DEBUGS HERE)
.Range("A2").Select
.Cells.EntireColumn.AutoFit

End With

"Dave Peterson" wrote:

You could try:

With Sheets("Sheet1")
.Select '<-- Added
.Cells.EntireColumn.AutoFit
.Cells.Select
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

You can only select cells on the selected sheet.

or without the selection:

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

And avoid the selecting completely.



Ant wrote:

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

--

Dave Peterson


--

Dave Peterson