CODEWITHSUNDEEP
excelvba
thelaw
thelaw

Reputation: 588

Error with copy paste due to merged column cells

I want to copy paste values from 4 columns in one worksheet into 4 columns of another worksheet depending on whether the values in a single column are null or not.

Following is my code:

Private Sub CommandButton1_Click()
    
Dim lastrow As Long, erow As Long

lastrow = Worksheets("jun").Cells(Rows.Count, 1).End(xlUp).Row
erow = 20

For i = 8 To lastrow
    If Worksheets("jun").Cells(i, 16).Value <> "" Then
        Worksheets("jun").Cells(i, 16).Copy
        Worksheets("jun").Paste Destination:=Worksheets("test").Cells(erow + 1, 1)
        Worksheets("jun").Cells(i, 3).Copy
        Worksheets("jun").Paste Destination:=Worksheets("test").Cells(erow + 1, 2)
        Worksheets("jun").Cells(i, 2).Copy
        Worksheets("jun").Paste Destination:=Worksheets("test").Cells(erow + 1, 3)
        Worksheets("jun").Cells(i, 6).Copy
        Worksheets("jun").Paste Destination:=Worksheets("test").Cells(erow + 1, 4)
        erow = erow + 1
    End If
Next i

End Sub

However this code produces an error when I try to paste the values into the second column of the test worksheet and I suspect this is because it is made of a merged column.

Following is the picture that shows the merged column. How could I combat this issue? enter image description here

Upvotes: 0

Views: 562

Answers (2)

Tragamor
Tragamor

Reputation: 3634

Having experimented slightly it appears .PasteSpecial Paste:=xlPasteValues will cause an error pasting into merged cells but .PasteSpecial Paste:=xlPasteValuesAndNumberFormats can paste into merged cells without that error

Try the updated sub below:

please note a slight alteration to the paste columns to take into account the merged column. I have also adjusted erow so it starts at 21 to avoid having to use 'erow + 1' for all entries

Private Sub CommandButton1_Click()
    
    Dim lastrow As Long, erow As Long
    With Worksheets("jun")
        erow = 21
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 8 To lastrow
            If .Cells(i, 16).Value <> "" Then
                .Cells(i, 16).Copy
                Worksheets("test").Cells(erow, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                .Cells(i, 3).Copy
                Worksheets("test").Cells(erow, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                .Cells(i, 2).Copy
                Worksheets("test").Cells(erow, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                .Cells(i, 6).Copy
                Worksheets("test").Cells(erow, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                erow = erow + 1
            End If
        Next i
    End With

End Sub

Upvotes: 1

TerrifiedJelly
TerrifiedJelly

Reputation: 135

Is this the line causing the issue?

Worksheets("jun").Paste Destination:=Worksheets("test").Cells(erow + 1, 3)

It sounds like you're trying to paste in the third column but when you merge columns, the left most column becomes the primary. For example, you've merged B&C (cols 2 and 3) so any reference to 3 is now redundant. You'd either paste in to column 2 or 4. With that row, if you're trying to paste in to column D, you'd need to reference column 4, not 3.

Upvotes: 1

Related Questions