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.
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.