have to find all PENDING ORDER from 9 work sheets in same workbook

[zoharb]

Hi ,
have to find all PENDING ORDER from 9 work sheets in same workbook
Zohar Batterywala

 

[mumps]

find all PENDING ORDER

If you mean find all rows that contain "Pending" in column J, what do you want to do with those rows after they are found? It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[zoharb]

If you mean find all rows that contain "Pending" in column J, what do you want to do with those rows after they are found? It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Respected,

I want all that rows at one place , so that particular department can be followed up for that pending order +

Download link
https://we.tl/t-OMvevkg4Pb
Zohar Batterywala

 

[mumps]

Do you want to copy all the "Pending" rows to a new sheet?

 

[zoharb]

yes sir

 

[mumps]

Start by inserting a new blank sheet and name it "Pending". Then try this macro:

Sub CopyPending()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, lRow As Long, desWS As Worksheet
    Set desWS = Sheets("Pending")
    For Each ws In Sheets
        If ws.Name <> "Pending" Then
            With ws
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If WorksheetFunction.CountIf(.Range("J1:J" & lRow), "*Pending*") > 1 Then
                    .Range("J1:J" & lRow).AutoFilter 1, "*Pending*"
                    .AutoFilter.Range.Offset(1).EntireRow.Copy desWS.Cells(desWS.Rows.Count, 1).End(xlUp).Offset(1)
                End If
                .Range("J1").AutoFilter
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[zoharb]

Start by inserting a new blank sheet and name it "Pending". Then try this macro:

Sub CopyPending()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, lRow As Long, desWS As Worksheet
    Set desWS = Sheets("Pending")
    For Each ws In Sheets
        If ws.Name <> "Pending" Then
            With ws
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If WorksheetFunction.CountIf(.Range("J1:J" & lRow), "*Pending*") > 1 Then
                    .Range("J1:J" & lRow).AutoFilter 1, "*Pending*"
                    .AutoFilter.Range.Offset(1).EntireRow.Copy desWS.Cells(desWS.Rows.Count, 1).End(xlUp).Offset(1)
                End If
                .Range("J1").AutoFilter
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[zoharb]

Respected Sir,
I am sorry, but I am not getting required output. Can you please find what mistake I am doing.
Zohar Batterywala

 

[zoharb]

Respected,
or can you please send me the excel file in which you have worked out the code
Zohar Batterywala

 

[mumps]

You have placed the macro in the worksheet code module. It should be in a regular module.
Do the following:
-make sure that macros are enabled in Excel
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.