Copy rows to other sheets based on value in column [Solved/Closed]

Report
Posts
2
Registration date
Thursday July 12, 2018
Status
Member
Last seen
July 13, 2018
-
Need_2Know
Posts
2
Registration date
Thursday July 12, 2018
Status
Member
Last seen
July 13, 2018
-
Hello,

I have worked through this topic and the have modified the code for my worksheet, but I am having a problem with the F5 function.
It works great when run from within the VB for applications application, but not when run from with in the worksheet. Any ideas to solve this problem.
Code below.

Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim xStr As String
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Register").UsedRange.Rows.Count
J = Worksheets("Open").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Open").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Register").Range("P2:P" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
xStr = CStr(xRg(K).Value)
If xStr = "ISSUED" Or xStr = "PENDING" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Open").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"
End Sub

1 reply


Build a button, and attach that code to it. The code CANNOT make changes to the sheet, without a HUMAN interaction. Which is why you can RUN it manually, but it won't change when you use the sheet. Click a button and it will work! This is by design, and there is no turning this off!
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3824 users have said thank you to us this month

Need_2Know
Posts
2
Registration date
Thursday July 12, 2018
Status
Member
Last seen
July 13, 2018

Thanks all sorted now. Appreciated.