0

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.

TylerH
  • 20,799
  • 66
  • 75
  • 101
BlackBear
  • 385
  • 1
  • 5
  • 25
  • 1
    `There are however merged cells directly below the table I am pasting into, if it matters.` The number of rows in NewData exceeds the number of rows of the table and hence when you try to paste special values it is hitting those merged cells... and hence the problem – Siddharth Rout Feb 22 '19 at 09:21
  • 2
    `NewData.Copy tbl.DataBodyRange(1, 1)` Should work. – Siddharth Rout Feb 22 '19 at 09:22
  • @siddarth rout Thank you very much! Is there a solution on how to not overwrite the merged cells but insert them in rows before? I am asking because there are some values in the merged cells I use as a reference point when determining the start position of my table. – BlackBear Feb 22 '19 at 09:28
  • 2
    Check the number of rows in the range that you are copying. Check the row number of the merged cell. If the former is greater than then later, insert new rows in the table. Repeat till former is lesser than the latter. Then do your paste :) – Siddharth Rout Feb 22 '19 at 09:48
  • And why not answer this question directly but as a comment, @Siddharth Rout? Then it's visible from the search page that this question is already answered. – simple-solution Feb 22 '19 at 09:52
  • @simple-solution: Yes I can but i wanted the OP to give it a try first :) – Siddharth Rout Feb 22 '19 at 09:54
  • @siddarthrout Hi so I found another solution that fits my agenda better. I will update the code accordingly, but thanks for the help! If you leave an answer I will make sure to upvote you for your help :) – BlackBear Feb 22 '19 at 09:56
  • Then you should post that solution ;) Not worried about upvotes here... – Siddharth Rout Feb 22 '19 at 10:05

1 Answers1

0

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.

BlackBear
  • 385
  • 1
  • 5
  • 25