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

 

[azad092]

hi

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

Hi
mumps
good morning
your help is great for me
thanks for your great work
now a I need a little help more
i want that instead of entering data into the separate sheets i.e MTN and SJA, I just enter data in Sheet MTN both for MTN and SJA but at end i.e column FD i want to use a check to move the data regarding SJA into SJA sheet
for example I want if I enter the word SJA in the column FD of sheet MTN the data of the entire row should be moved into the next blank row the sheet SJA
but there is an important point that form the entire row only the simple data will be moved into the SJA sheet, not the formulas because both sheet have same data entries and formulas, but in reports the formulas are based on MTN and SJA sheets separately.
I hope you will understand my need and i hope you will also be helpful for me
thanking you in anticipation

 

[mumps]

The current macro filters the data based on columns AC and AD in both sheets "MTN" and "SJA" and then copies the filtered data to the "DETAILS" sheet. I assume that you will enter "SJA" in a column in the "MTN" sheet before the data is filtered and copied to the "DETAILS" sheet. Is this correct? Do you want two separate macros, one to copy the data from MTN to SJA and one to copy the data to DETAILS or do you want one macro to do everything?

 

[azad092]

yes you are right
one macro that you have made for coping data from MTN and SJA into DETAIL Sheet
and an other is needed to move data from MTN to SJA incase of if I ener SJA in column FD in Sheet MTN
for the first macro, there is little thing that I want to modify, instead of the whole row we need only specific columns data. these columns you can match in the DETAIL Sheet with MTN and SJA Sheet

PES AGRS 1122 test (1).xlsm

drive.google.com

 

[mumps]

Click here to download your file. I've had to modify the DETAILS sheet to get the macro in Module7 to work.

move data from MTN to SJA incase of if I enter SJA in column FD in Sheet MTN

I tried to design a macro to do what you described in the above request but I couldn't get it to work because I kept getting errors about missing references. You also had additional data and formulas further down in the sheet that interfered with the code. Also you have all sorts of hidden rows and columns.

I hope the macro in Module 7 works for you. I don't think that I will be able to help any further.

 

[azad092]

the first macro is more useful for coping data, but your each help is valuable
thanks dear

 

[mumps]

My pleasure.