Moving rows automatically based on cell value

[Skernit2]

Hi,

I want a macro to move a row based on the cell value into a specific sheets. ​

I have two questions and would really appreciate your help. Especially the second is important.

1. If status is "Ready for Repair" move to Sheet "Repair" and so on...

The following macro is working but I would like to add multiple status checks.... how can I do that?



2. One macro should check for all sheets if the status is "Resent/Picked-Up" and move it to the sheet "Archive"

How can I alter the above macro so it checks for all sheets except the ARchive one?

Sub Repair()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Repair Form").UsedRange.Rows.Count
J = Worksheets("Repair").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Repair").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Repair Form").Range("A2:A" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Ready for Repair" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Repair").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Ready for Repair" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub​

 

[hatstand]

Hi,

You can either add this to your code to cycle through your sheets or use it to call your code. Hope it helps

Sub DontUseSheet()

Dim WsCurr As Worksheet

For Each WsCurr In ThisWorkbook.Worksheets

If WsCurr.Name <> "Repair" Then 'Change Sheet2 and add any other sheets ytou have that do not need to be checked.
'Your Code or call your code
End If

Next WsCurr

End Sub

 

[Skernit2]

Hi,

You can either add this to your code to cycle through your sheets or use it to call your code. Hope it helps

Sub DontUseSheet()

Dim WsCurr As Worksheet

For Each WsCurr In ThisWorkbook.Worksheets

If WsCurr.Name <> "Repair" Then 'Change Sheet2 and add any other sheets ytou have that do not need to be checked.
'Your Code or call your code
End If

Next WsCurr

End Sub

Hi, thanks for the quick reply. I am quite new to using this and I am still struggling with the coding/language.

What do you mean with call your code?


I have just realized that it will get quite big if with multiple sheets and different macros. Is it possible to code that it will check the "Status" in the first column of all sheets and then move/sort the row to the respective sheet?

That would help me a lot :D

 

[hatstand]

Hi,

Think of it this way. The code I posted will check every sheet one after another, missing out the ones you do not need to checked. When it comes to a sheet that you want checking it will run the code you have written. To do this you would replace the line ''Your Code or call your code' with 'Call Repair'.

If you want to add further tests you can i.e. check for Status, you could write another bit of code to do this or incorporate it into your existing code.

Did you write the code or are you modifying someone else's code?

 

[Skernit2]

I did modify somebody elses. Thats why I am also not too sure