Search code examples
excelvbaexcel-tables

VBA: Error 1004 merged cells when copying data into a table


The objective is to a copy a range of data into a prexisting table, where the table first removes the old data. I have additional code but this is the part of the code that handles the table data. Important note: The range I am copying does NOT contain merged cells. I have checked and double checked. There are however merged cells directly below the table I am pasting into, if it matters.

Sub updateData()
   Dim ws as worksheet, Tws as worksheet
   Dim tbl as listobject
   Set ws = thisworkbook.worksheets(1)
   Set Tws = thisworkbook.Sheets(2)
   Set tbl = Ws.ListObjects(1)

                        With tbl ' Deleting data from current table in the worksheet
                            If Not .DataBodyRange Is Nothing Then
                                .DataBodyRange.Delete
                            End If
                            .ListRows.Add
                        End With
                    fr = WorksheetFunction.Match("LookUpValue", Ws.Columns(1), 0) - 3 ' First row
                    fc = 1 ' First column
                    lc = Tws.Cells(fr, fc).End(xlToRight).Column ' Last column
                    lr = Tws.Cells(fr, fc).End(xlDown).Row - 3 ' Last row
                    Set NewData = Tws.Range(Tws.Cells(fr, fc), Tws.Cells(lr, lc))
                    NewData.Copy tbl.DataBodyRange(1, 1) ' WORKING CODE (overwrites merged cells)
                    Application.CutCopyMode = False
End sub

EDIT: Just to clarify the error I receive is run-time error '1004': To do this, all merged cells need to be of the same size

2nd EDIT: Thanks to @siddarth routh I have a working code. Personally instead of NewData.Copy tbl.DataBodyRange(1, 1) which works fine for overwriting merged cells I decided to unmerge the cells below to keep insert my rows between them and use the lines

 NewData.Copy
 tbl.DataBodyRange(1, 1).PasteSpecial

To keep formatting and not overwrite my cells.


Solution

  • Using

    NewData.Copy
    tbl.DataBodyRange(1, 1).PasteSpecial
    

    and unmerging the cells below my table works. Also, I added the line .listrows.add in my with tbl clause.