Excel VBA paste offset from activecell to merged cell

0
votes

I am copy - pasting values from one worksheet to another. The problem is that I have two merged cells where I want to input my data, these are D:E. Same data from B67 goes to two merged cells which are located in Offset(-1, -1) and Offset(-24, 0)

My code: 

Private Sub CommandButton2_Click()

'Paste to a Defined Range
ThisWorkbook.Sheets("Other Data").Range("L67").Copy

'Offset Paste (offsets 2 cells down and 1 to the right
ActiveCell.PasteSpecial xlPasteValues

ThisWorkbook.Sheets("Other Data").Range("B67").Copy
ActiveCell.Offset(-1, -1).PasteSpecial xlPasteValues
ActiveCell.Offset(-24, 0).PasteSpecial xlPasteValues

End Sub

I receive an error on:

ActiveCell.Offset(-1, -1).PasteSpecial xlPasteValues

This cell is located 1 cell up and 1 to the left. If I unmerge this cell the code works fine. However it should be merged to fit my text.

The same with:

ActiveCell.Offset(-24, 0).PasteSpecial xlPasteValues
2
excelvba

2 Answers

2
votes

Hi I think it is connected to the xlpastevalues. Try using xlPasteAll and see if that fixes your issue.

1
votes

This will work. 

Private Sub CommandButton2_Click()

    Dim Temp As Variant
    Dim R As Long

    Temp = ThisWorkbook.Sheets("Other Data").Range("L67").Value
    With ActiveCell
        R = .Row
        If R > 1 And .Column > 1 Then .Offset(-1, -1).MergeArea.Value = Temp
        If R > 24 Then .Offset(-24, 0).MergeArea.Value = Temp
    End With
End Sub