CODEWITHSUNDEEP
excelvba
Nat
Nat

Reputation: 53

Error 1004: Excel VBA

The error occurs at ActiveSheet.Cells(q,1).Offset(2,1).PasteSpecial.xlPasteValues

Was wondering why I am faced with a runtime error 1004. It mentions that the information cannot be pasted as copy area and paste area are not the same size and shape. Is there a work around?

                     For i = 1 To testrows
                            For j = 1 To testcols

                                If (range1.Cells(i, j).Value <> range2.Cells(i, j).Value) Then
                                     'Conclude that range dimension is not the same
                                     bMatches = False
                                     i = testrows
                                     j = testcols
                                     'Exit loops
                                End If
                            Next
                        Next
                    End If

                    'If ranges of two comparison sheets are the same
                    If bMatches Then
                        rowmatched = True
                        k = referencesheetcols
                    End If

                    'Sheets(outputsheetname).Cells(1, 1).Value = rowmatched
                    'Set place to paste data
                    If (Not (rowmatched) And k = referencesheetcols) Then
                        range2.Copy
                        Sheets(referencesheetname).Cells(p, 1).Offset(2, 0).Select
                        ActiveSheet.Paste
                        p = p + 1
                        Sheets("Datasheet").Activate
                        'ActiveSheet.Cells(q, 1).Offset(2, 1).Select
                        ActiveSheet.Cells(q, 1).Offset(2, 1).PasteSpecial xlPasteValues
                        'ActiveSheet.PasteSpecial xlPasteValues
                        q = q + 1
                    End If
                    Next
            End If
        Next

    End Sub`enter code here`

Upvotes: 1

Views: 203

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

'.....
If (Not (rowmatched) And k = referencesheetcols) Then

    range2.Copy Sheets(referencesheetname).Cells(p, 1).Offset(2, 0)
    p = p + 1

    Sheets("Datasheet").Cells(q, 1).Offset(2, 1).Resize( _
        range2.Rows.Count, range2.Columns.Count).Value = range2.Value
    q = q + 1

End If
'.....

Upvotes: 1

Related Questions