Can someone tell me what is wrong with this code?

A

Ant

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

TH6

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

Ant

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

TH6

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
 
A

Ant

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

Dave Peterson

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

Ant

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
 
D

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

Dana DeLouis

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
 
Top