Move Row

[Johnboy]

I use the following to move and delete row for a one worksheet, I would like one macro to do the same for all sheets except "Master" and "Discontinued" in the workbook.

Sub move_if_Discontinued()

r = 7
Do Until Sheets("Brick").Cells(r, 2).Value = ""

If Sheets("Brick").Cells(r, 10).Value = "Yes" Or _
Sheets("Brick").Cells(r, 10).Value = "YES" Or _
Sheets("Brick").Cells(r, 10).Value = "yes" Then

Sheets("Discontinued").Rows(7).EntireRow.Insert
Rng = "a" & r & ":k" & r
Sheets("Brick").Range(Rng).Copy _
Destination:=Sheets("Discontinued").Range("a7")

Sheets("Brick").Rows(r).EntireRow.Delete

End If

r = r + 1
Loop

End Sub

Thanks
Johnboy

 

[wigi]

Try this code:

Sub move_if_Discontinued()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        If ws.Name <> "Master" And ws.Name <> "Discontinued" Then

            r = 7
            Do Until ws.Cells(r, 2).Value = ""

                If UCase(ws.Cells(r, 10).Value) = "YES" Then

                    ThisWorkbook.Sheets("Discontinued").Rows(7).Insert
                    ws.Range("A" & r & ":K" & r).Copy Destination:=ThisWorkbook.Sheets("Discontinued").Range("A7")
                    ws.Rows(r).Delete

                End If

                r = r + 1

            Loop

        End If

    Next

End Sub

However, you are looping through the records in the wrong way: you should loop backwards (from last row to row 7). I'll leave this up to you.

Also, look at the other changes w.r.t. your initial macro.

Can you plus use

 tags? Thanks.

 

[Johnboy]

Thanks wigi... Tried to download VB HTML Maker but my company has restricted certain downloads. Again thanks for your help.

 

[wigi]

Thanks wigi... Tried to download VB HTML Maker but my company has restricted certain downloads. Again thanks for your help.

You should just type:

paste your code, and type again

[FORWARD SLASHcode]

Replace the FORWARD SLASH with /