Move all pending Faults

[Nitinkumar]

Hello ,

myself a new user , i have an excel file for faults arrived during the month for three separate locations in the form of three sheets. i want all pending faults under column "Status" from these three sheets into one sheet.

Kindly help me please.

Regards,

Nitin

[arlu1201]

Which are the 3 sheets? Should the rows be copied to a new sheet?

[Nitinkumar]

Thanks for response .

Rows are required to be moved from sheets-TP01, TP02,and TP03. yes rows should be moved to another sheet named "Pending".

Nitin

[arlu1201]

Try this code

Sub pending()
Dim i As Long, lrow As Long
Dim copyrange As Range

Application.ScreenUpdating = False

Worksheets.Add.Name = "Pending"
Worksheets("TP01").Rows("5:6").Copy Worksheets("Pending").Rows("5:5")

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "TP*" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            If lrow > 5 Then
                .Range("A5:AX" & lrow).AutoFilter field:=17, Criteria1:="Pending"
                On Error Resume Next
                Set copyrange = .Range("A7:AX" & lrow).SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not copyrange Is Nothing Then
                    copyrange.Copy Worksheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                    Application.CutCopyMode = False
                End If
                Set copyrange = Nothing
                .Range("A5:AX" & lrow).AutoFilter
            End If
        End If
    End With
Next i

MsgBox "Done"

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[Nitinkumar]

Hello Sir,

Thanks a lot for sparing time on my request from bottom of my heart.i am not very good at english to say thanks. Code worked fine as it copies data for status "pending" faults to a sheet "Pending". May be i did not explain well enough. What i want is that it keeps on copying all the pending faults from TP01, TP02, TP03 without creating a new sheet everytime also it adds to row below in pending sheet where already some pending faults have been copied by code. Right now it is copying only from TP01.
If it can be done with a command button will be very good

Thanks in advance.

Nitin

[arlu1201]

This is the updated code -

Option Explicit

Sub pending()
Dim i As Long, lrow As Long
Dim copyrange As Range

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "TP*" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            If lrow > 5 Then
                .Range("A5:AX" & lrow).AutoFilter field:=17, Criteria1:="Pending"
                On Error Resume Next
                Set copyrange = .Range("A7:AX" & lrow).SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not copyrange Is Nothing Then
                    copyrange.Copy Worksheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                    Application.CutCopyMode = False
                End If
                Set copyrange = Nothing
                .Range("A5:AX" & lrow).AutoFilter
            End If
        End If
    End With
Next i

MsgBox "Done"

Application.ScreenUpdating = True

End Sub

If you want to assign it to a button, go to the Tools box and you can drag a button onto your page, name it and then assign the macro to it.

[Nitinkumar]

Hello Sir,

Greetings..!!
Thanks for prompt response
I have attached excel file in which i have pasted the code and the way i was wanting it to work. Somehow may be due to my mistake code is not working.Request please see attached file in "Pending" sheet. I want all pending faults to be updated when button is pressed but not duplicated.

Request your valuable time again.

Thanks in advance..

Nitin

[arlu1201]

The code was nowhere in your file.

So if the user runs the macro twice, it is duplicating the rows, right? Then we can run a small delete duplicates code which will remove the duplicates based on the Fault no.

[Nitinkumar]

Sorry Sir i have attached other file . Please see the attached.. Code is not working . I want like i have already created a sheet named pending. Now i want all the faults with status pending from sheets TP01, TP02,Tp03 copied to pending sheet when i click the button. if already all the faults are there code should do nothing. and if more pending faults are added in TP* sheets and again i press the button it should copy the remaining pending faults. I have attached file now with code.

Thanks for support Sir

[arlu1201]

See the attached file. When you click on the Design mode in the developer menu, you need to click on the button and remove the formula in the formula bar. Then right click and assign macro. Then it works.

[Nitinkumar]

It's awesome sir thanks a ton.. a very small problem . If one pending fault it has copied then again if a fault is pending in TP* sheets, it will duplicate the rows . can it be fixed. Please see in pending sheet , fault no.2737 is duplicated. how ever if not possible i can still manage . i wil run the code once in a month only. i have attached the file for your pesual again.
Thanks you so much

[arlu1201]

Add these lines

With Worksheets("Pending")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A4:R" & lrow).RemoveDuplicates Columns:=2, Header:=xlNo
End With

before this line

Msgbox "Done"

[Nitinkumar]

Hello Sir,

This time its THE PERFECT .. thanks thanks a lot

Regards
Nitin

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.