VBA Copy and PasteSpecial puts nothing in destination range but appears to select it

[Kirk Fleming]

This is virtually identical to the actual code.

Sub Test()

Worksheets("Sheet1").Activate
Range(Cells(1, 1), Cells(10, 13)).Copy Destination:=Worksheets("Sheet2").Range("A2")
' and I've tried the alternative:
' Range(Cells(1, 1), Cells(10, 13)).Copy
' Sheets("Sheet2").Range("A2").PasteSpecial xlPasteValuesAndNumberFormats

End Sub

I've checked all the real basic stuff that I know to check: source is actually populated with data, pasted values are not in any way hidden, worksheet references checked and double-checked.

After successful execution, the destination range has no data in it, but it is selected in the sense it has a box around it and is gray. In the original code, source range is actually Range(Cells(1,1), Cells(iRowNum, 13)), where iRowNum is dimensioned LONG and has a valid value assigned. In that case, however, the destination range is not boxed with a solid line and the cells gray, but rather the range has your moving dashed-line border and the cells have no fill. But still no data.

I've tried quite a number of different approaches; if it runs at all, I get this unsatisfying result. I've tried activating the sheets in turn, etc., it makes no difference (nor would I expect it to).

My level of VBA experience is pre-beginner.

[Stewart Ross]

Hi. The copy sub works fine for me from within an Excel workbook with test data.

It is not an optimal way to do the copy as there is actually no need to select a worksheet before you copy a range, but regardless it does work just as it should.

Are you running it directly within Excel? This is important to know, as what works in Excel will not work when using Excel as an automation server without full qualification of the Excel instance involved.

The following does the copy without selecting the sheet first. It does not use the cells method, which is not suitable for use in the form listed without qualification as it is not referring to the same range:

Worksheets("Sheet1").Range("A1:M10").Copy Destination:=Worksheets("Sheet2").Range("A2")

If you need to use the Cells method you can ensure that the correct range is being referred to by using With and using the dot qualifier to specify that the cells method is within the Sheet1 object range:

With Worksheets("Sheet1")
 .Range(.Cells(1, 1), .Cells(10, 13)).Copy Destination:=Worksheets("Sheet2").Range("A2")
End With

All three of these - your original and the two listed above - work fine for me.

-Stewart