CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

Excel VBA paste offset from activecell to merged cell

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

Upvotes: 0

Views: 336

Answers (3)

Uno Buscando
Uno Buscando

Reputation: 31

Since we are copying only values, wouldn't it be easier to just do this?

ActiveCell.Offset(-1, -1) = Range("B67")

Or if the formula is different from the value:

ActiveCell.Offset(-1, -1).Value = Range("B67").Value

Upvotes: 1

Variatus
Variatus

Reputation: 14373

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

Upvotes: 2

RCL
RCL

Reputation: 276

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

Upvotes: 2

Related Questions