I have a VBA code to move rows in the "Data" worksheet to another sheet, "Active Status" based on the status in Column A. The "Data" spreadsheet will be modified by users on a daily basis. The status may change from Active to Non-Active and other information may be added. I need help regarding moving the rows in the worksheet "Data" to the "Active Status" worksheet and overwriting the data that is currently in the "Active Status" worksheet. Any suggestions? My current code keeps adding to the rows to the "Active Status" worksheet and not overwriting them. That is the part that I cannot figure out.
Below is an example of the data and the VBA code I am currently using.
Sub MoveActive()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Data").UsedRange.Rows.Count
J = Worksheets("Active Status").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Active SH Referrals").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Data").Range("A1:C" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Active Status" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Active Status").Range("A" & J)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Sample of data:
Below is an example of the data and the VBA code I am currently using.
Sub MoveActive()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Data").UsedRange.Rows.Count
J = Worksheets("Active Status").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Active SH Referrals").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Data").Range("A1:C" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Active Status" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Active Status").Range("A" & J)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Sample of data:
Active Status | Date | Month | Year | Day of the Week | Name |
Active | 7/12/2022 | July | 2022 | Tuesday | xxxxx |
Non Active | 7/12/2022 | July | 2022 | Tuesday | xxxxx |
Active | 7/23/2022 | July | 2022 | Saturday | xxxxx |