Moving rows automatically based on cell value

Skernit2

New Member
Joined
Sep 28, 2018
Messages
4
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

Well-known Member
Joined
Mar 17, 2005
Messages
729
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

New Member
Joined
Sep 28, 2018
Messages
4
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

Well-known Member
Joined
Mar 17, 2005
Messages
729
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?
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top