ActiveSheet.paste with offset giving error

Question

Hello I am new to VBA and I was designing a program. It simply selects Sheet2 and copies something from cell A1. It then selects Sheet1 and offsets from cell A1 and pastes to the newly selected cell. The code is as follows.

Sub Test()
   Worksheets("Sheet2").Activate
   ActiveSheet.Range("A1").Activate
   Selection.Copy
   Worksheets("Sheet1").Activate
   ActiveSheet.Range("A1").Activate
   ActiveCell.Offset(1, 1).Activate
   ActiveSheet.PasteSpecial
End Sub

The strange thing is that if I change the line .Offset(1, 1) to .Offset(1) the program will work correctly. If I do not I get a Run-time error '1004' Application-defined or object-defined error.

I am aware that there are better ways to go about this than using selections and while those are interesting to me I would also like to know the reason for the problem.

Thank you for your help.

score 2 · Answer 1 · answered Aug 16 '15 at 05:27

2

The reason you might be having issues it that to perform a pastespecial you should have a cell selected, going through microsofts guide they've outlined it below. Since you are new to VBA it is good to avoid .activate its good habit to get into copy/paste without selecting a cell. An example for your code would be:

Sub Test() Worksheets("Sheet2").Range("A1").Copy Worksheets("Sheet1").Range("A1").Offset(1,1).Paste End Sub

Microsoft

answered Aug 16 '15 at 05:27

Jake Bourne

723
3
10
27

Hi I tried this sample code and it doesn't work. It has a problem with the 2nd line it gives the error "run-time error '438': Object doesn't support this property or method". Is there a setting I am supposed to have turned on or something? – Zasekle Aug 16 '15 at 16:11

score 0 · Answer 2 · edited May 23 '17 at 12:06

It is simpler just to run an exact copy like below to put the copy in as the last sheet

    Sub Test()
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Master")
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
    End Sub

** Copy an entire worksheet to a new worksheet in Excel 2010

You can also use the Below Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")

    'Method 2
    'Copy the data
    Sheets("Sheet1").Range("A1:B10").Copy
    'Activate the destination worksheet
    Sheets("Sheet2").Activate
    'Select the target range
    Range("E1").Select
    'Paste in the target destination
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

I believe only Sheet1!B2 was intended to receive the contents and formatting of Sheet1!A1. I don't believe entire worksheets were involved unless the OP neglected to state the [Range.CurrentRegion property](https://msdn.microsoft.com/en-us/library/office/ff196678.aspx). — , Aug 16 '15 at 07:17

score 0 · Accepted Answer · edited May 23 '17 at 10:26

The reason your code is not working is due to the the overuse of the Range .Activate method. This is different from .Range.Select in that it only makes a different cell in the selection the ActiveCell. If the worksheet's Selection object only encompasses a single cell or a range of cells outside of what you are activating, then yes you will will select a single cell. If it is within the current worksheet's selected cells the it will only transfer user control to that cell.

Select a range of cells on a worksheet. For the purposes of this example C3:E8. Depending upon how you have selectected the cells, C3 is most likely in a different colour state and holds the ActiveCell property for that worksheet. It will receive any user input.

Now tap Enter↵ a few times. You will see that the 'focus' (aka ActiveCell) changes as you tap the Enter key. While C3:E8 may be the worksheet's Selection property, the worksheet's ActiveCell property changes as you tap Enter↵.

If you are pasting an unknown number of cells into a target destination, you should pick a single cell in the upper-left corner of where you want the cells to reside. Having a target of multiple cells with one cell somewhere within the range as the active cell will not work unless by circumstance and circumstance is not a reliable programming paradigm.

In short, if you .Activate a cell you will only be selecting a single cell if the cell you activate is outside the current selection. If you .Select a cell the that is the only one selected and is the active cell on that worksheet. You cannot paste into a range of cells that is a different rows × columns size than the source unless it is a single cell.

Your code should work with a simple modification.

Sub Test()
   Worksheets("Sheet2").Activate
   ActiveSheet.Range("A1").Activate
   Selection.Copy
   Worksheets("Sheet1").Activate
   ActiveSheet.Range("A1").SELECT   '<~~ this is changed to .Select
   ActiveCell.Offset(1, 1).Activate
   ActiveSheet.PasteSpecial
End Sub

However, there may be better ways. Direct Copy and paste is a preferred alternative but if all you want is the Range.Value property then a direct value transfer is best.

'abbreviated Copy and Paste
Sub Test2()
    With Worksheets("Sheet2")
        .Range("A1").Copy _
            Destination:=Worksheets("Sheet1").Range("A1").Offset(1, 1)
    End With
End Sub

'direct value transfer
Sub Test3()
    With Worksheets("Sheet1").Range("A1").Offset(1, 1)
        .Value = Worksheets("Sheet2").Range("A1").Value
    End With
End Sub

If you do not need to transfer cell formatting, the latter is preferred.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

ActiveSheet.paste with offset giving error

3 Answers3