Copy a row into another sheet based on cell value

[azad092]

hi
dear members
good afternoon
i have data entry file have different worksheets
I want to copy a row data into another sheet
there are tow Sheets MTN and SJA, in both sheets I enter data.
another sheet named DETAIl I wants to copy the data based on a cell value
for example
For MTN Sheet
if Column AC2 Or AD2 Or Both AC2:AD2 having the value of >=1 then
the entire row should be copied to the DETAIL sheet
the same process should be applied for the SJA Sheet
if anyone knows about the vba coding please help me

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim lastrow As Long, ws As Worksheet, desWS As Worksheet, rngAC As Range, rngAD As Range
    Set desWS = Sheets("DETAIL")
    lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ws In Sheets(Array("MTN", "SJA"))
        With ws
            .Cells(1, 1).CurrentRegion.AutoFilter 29, ">=1"
            Set rngAC = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            If .AutoFilterMode Then .AutoFilterMode = False
            .Cells(1, 1).CurrentRegion.AutoFilter 30, ">=1"
            Set rngAD = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            If .AutoFilterMode Then .AutoFilterMode = False
            Union(rngAC, rngAD).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[azad092]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim lastrow As Long, ws As Worksheet, desWS As Worksheet, rngAC As Range, rngAD As Range
    Set desWS = Sheets("DETAIL")
    lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ws In Sheets(Array("MTN", "SJA"))
        With ws
            .Cells(1, 1).CurrentRegion.AutoFilter 29, ">=1"
            Set rngAC = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            If .AutoFilterMode Then .AutoFilterMode = False
            .Cells(1, 1).CurrentRegion.AutoFilter 30, ">=1"
            Set rngAD = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            If .AutoFilterMode Then .AutoFilterMode = False
            Union(rngAC, rngAD).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

Hi
dear mumps
thanks for your kind attention
I apply your suggested coding but it giving an error that you can see in the image

 

[mumps]

Do you have headers in row 1 in each sheet with your data starting in row 2 and no blank rows? 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 one or two of your sheets. 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).

 

[azad092]

dear
hear you can find the file

PES AGRS 1122 (Repaired) - Updated.xlsm

drive.google.com

 

[mumps]

Your file is password protected and generates an error on opening. Remove all the macros in the workbook, unprotect it and attach a copy here.

 

[azad092]

hi
mumps good morning
i have removed the password and macros

PES AGRS 1122 test.xlsm

drive.google.com

 

[mumps]

Your link takes me to the "create and account" page in Google Drive. I need a direct link to your file.

 

[azad092]

hi
good morning
i hope this link will be useful for you

PES AGRS 1122 test.xlsm

drive.google.com

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet, rngAC As Range, rngAD As Range, rowCount As Long, x As Long: x = 1
    Set desWS = Sheets("DETAIL")
    For Each ws In Sheets(Array("MTN", "SJA"))
        With ws
            .ListObjects("Table" & x).Range.AutoFilter Field:=29, Criteria1:=">=1"
            rowCount = .[subtotal(103,A:A)] - 1
            If rowCount > 0 Then
                Set rngAC = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            End If
            .Range("A1").AutoFilter
            .ListObjects("Table" & x).Range.AutoFilter Field:=30, Criteria1:=">=1"
            rowCount = .[subtotal(103,A:A)] - 1
            If rowCount > 0 Then
                Set rngAD = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            End If
            .Range("A1").AutoFilter
            If Not rngAC Is Nothing And rngAD Is Nothing Then
                rngAC.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            ElseIf rngAC Is Nothing And Not rngAD Is Nothing Then
                rngAD.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            ElseIf Not rngAC Is Nothing And Not rngAD Is Nothing Then
                Union(rngAC, rngAD).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            End If
            x = x + 1
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub