MeMikeExcel
New Member
Hello,
I've created a worksheet that contains real time search box for data in a table.
I am trying to find and filter a specific column that do not have empty cells in it.
Then, copy the entire contents of the filtered rows excluding the header row to another worksheet from the same workbook.
After the copy is done, delete the copied lines from the source sheet.
Due to the fact, the data are in a table, when I use CurrentRegion to select the filtered rows, the codes selected the header row as well.
Is there a way for the codes to copy only the filtered rows excluding the header row from the table to another sheet ?
Also, an error pops up when attempting to delete the copied rows in the source sheet. Why?
If no table were used on worksheet, then the codes work fine. Any help or suggestion to resolve or work around the codes would be greatly appreciated.
Thank you for your help in advance.
Here is what I have coded:
I have two real time Search Boxes and the below vba codes are in the Worksheet
>>> use code - tags <<<
The below vba codes are in the Module 1 assign to a Button for a user to use whenever applicable.
I've created a worksheet that contains real time search box for data in a table.
I am trying to find and filter a specific column that do not have empty cells in it.
Then, copy the entire contents of the filtered rows excluding the header row to another worksheet from the same workbook.
After the copy is done, delete the copied lines from the source sheet.
Due to the fact, the data are in a table, when I use CurrentRegion to select the filtered rows, the codes selected the header row as well.
Is there a way for the codes to copy only the filtered rows excluding the header row from the table to another sheet ?
Also, an error pops up when attempting to delete the copied rows in the source sheet. Why?
If no table were used on worksheet, then the codes work fine. Any help or suggestion to resolve or work around the codes would be greatly appreciated.
Thank you for your help in advance.
Here is what I have coded:
I have two real time Search Boxes and the below vba codes are in the Worksheet
>>> use code - tags <<<
Code:
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table1").Range.AutoFilter field:=3, Criteria1:=[F3] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub
Private Sub TextBox2_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table1").Range.AutoFilter field:=4, Criteria1:=[J3] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End SubThe below vba codes are in the Module 1 assign to a Button for a user to use whenever applicable.
Code:
Sub RmCmpTrade1()
Dim LRow As Long, Fltr As Range, Fltr1 As Range
Sheets("Outstanding").Activate
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set Fltr = Rows(5).Find("TradeStatus")
Fltr.AutoFilter field:=Fltr.Column, Criteria1:="<>"
Fltr.Select
Set Fltr1 = ActiveCell.End(xlDown)
If Fltr1.Value <> "" Then
Fltr1.Select
Sheets("Outstanding").Rows(5).Offset(1).CurrentRegion.Copy
Sheets("Completed").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
ActiveCell.CurrentRegion.EntireRow.Delete '//ERROR POPS UP on this line of code //
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Else
Range("A5").Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
MsgBox "There is no completed trade to move.", vbInformation, "Message Box"
End If
End Sub
Last edited by a moderator:
