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

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

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

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

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

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 - 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

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

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