I think the problem lies with the dot before the Cells.Select
It does work fine if you are in Sheet1, but if you are in, say Sheet2, and
run the macro then Cells.Select will select all cells in Sheet2, not Sheet1
which is what I want. Ultimately I am wanting to run the macro from, say
Sheet2 and for it to visibly stay on Sheet2 whilst the macro does it's thing
in Sheet1 - if you get my drift.

"TH6" wrote:

Try changing the order of the lines:

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

Works for me.

TH6

"Ant" wrote:

The debug occurs though on the line:

.Cells.Select

Also - it appears your solution is exactly the same as mine. Am I missing
something small perhaps?

Thanks in advance.

"TH6" wrote:

You have a bad line break:
use
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
or
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks _



"Ant" wrote:

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