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

Posts
2
Registration date
Thursday July 12, 2018
Last seen
July 13, 2018
- Jul 12, 2018 at 10:08 AM - Latest reply:
Posts
2
Registration date
Thursday July 12, 2018
Last seen
July 13, 2018
- Jul 13, 2018 at 02:43 AM
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

See more 

Your reply

2 replies

Best answer
Posts
10873
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 20, 2018
Jul 12, 2018 at 03:38 PM
1
Thank you
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!

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1701 users this month

Posts
2
Registration date
Thursday July 12, 2018
Last seen
July 13, 2018
- Jul 13, 2018 at 02:43 AM
Thanks all sorted now. Appreciated.
Respond to ac3mark