0

I have a code that adds rows on my table, it works like this.

Copy entire row based on current cell location (Red row). enter image description here

Then paste it below the current cell location by adding rows, not just copying the data enter image description here

Error im getting: Error 1004 - Cannot move cells in a table or filtered range

The problem is, I can't get this code to work when my table has filters, how do I do this without breaking my current code?

My code:

Sub INSERT_ROWS()

Application.ScreenUpdating = False

    Dim MyTable As Object
    Dim RowsToAdd As Long
    RowsToAdd = Worksheets("CC").Range("B18") 'Where i get the number of rows to be added
        
    Set MyTable = ActiveSheet.ListObjects(1)
    
    If RowsToAdd > 0 Then
            If Not Intersect(Selection, MyTable.DataBodyRange) Is Nothing Then
            
            Dim SelectedRow As Long
            SelectedRow = Intersect(Selection, MyTable.DataBodyRange).Row - MyTable.HeaderRowRange.Row
            
            Dim RowCounter As Long
            For RowCounter = SelectedRow To SelectedRow + RowsToAdd - 2 'To place rows bellow current cell location
                MyTable.ListRows.Add Position:=RowCounter + 1
                MyTable.ListRows(RowCounter).Range.Copy Destination:=MyTable.ListRows(RowCounter + 1).Range
            Next RowCounter
        End If
    End If

Application.ScreenUpdating = True

End Sub
Cooper
  • 229
  • 1
  • 8
  • How is the behavior of your code different when filters have been applied? – Tim Williams Dec 12 '22 at 17:20
  • Error 1004 - Cannot move cells in a table or filtered range – Cooper Dec 12 '22 at 17:50
  • 1
    From https://stackoverflow.com/questions/68195097/vba-save-autofilter-do-manual-stuff-then-reaply-filters, this page https://learn.microsoft.com/en-us/office/vba/api/excel.autofilter has code for storing filter settings and then re-applying them later. So you could maybe remove the filters after reading the settings, and then add the rows and re-apply the filters. – Tim Williams Dec 12 '22 at 18:30
  • 1
    In my side, I tried like this : `With ActiveSheet.ListObjects(1)` ... next line `r = ActiveCell.Row - .HeaderRowRange.Row: arr = .ListRows(r).Range.Value` this line put the value of the selected row of the table as arr variable. Next line `For i = 1 To RowsToAdd` ... next line `ActiveCell.EntireRow.Insert:.ListRows(r).Range.Value = arr` ... next line `next i` ... next line `end with`. It doesn't throw me an error although the table is being filtered. – karma Dec 13 '22 at 03:18
  • @karma Your code is placing the cells above the active cell, so it is getting the header formatting, how can I modify this and put it to place the cells below the active cell? – Cooper Dec 13 '22 at 13:18
  • @TimWilliams, thanks for the tip, I'll take a look at those posts, and try to work on my own code – Cooper Dec 13 '22 at 13:20
  • @Cooper, in my side I add `col = .HeaderRowRange.Columns.Count:ColorID = ActiveCell.Interior.Color` - then inside the loop (just befor the next i) I add `ActiveCell.Resize(1, col).Interior.Color = ColorID`. – karma Dec 13 '22 at 14:26

0 Answers0