Can someone tell me what is wrong with this code?
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
|