update values between sheets

[john_cash]

Hi to all,
in the attached workbook you have to do this
in "articoli" sheet
IF E6 < J6 OR E6 = J5
bring the row into the sheet "sotto_scorta"
I hope I have explained
thanks
john

[Kaper]

try such macro:

Sub passare_a_sotto_scorta()
Dim i As Long
Application.ScreenUpdating = False
With Sheets("articoli")
  For i = .Cells(.Rows.Count, "A").End(xlUp).Row To 6 Step -1
    If .Cells(i, "E") <= .Cells(i, "J") Then
      .Rows(i).Cut Sheets("sotto_scorta").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).EntireRow
    End If
  Next i
End With
Application.ScreenUpdating = True
End Sub

[john_cash]

Hi kaper, thank you
A change:
in the "articoli" sheet the lines must remain in the same place, they must not be eliminated

[mohadin]

Sub passare_a_sotto_scorta()
    Dim i As Long
    Application.ScreenUpdating = False
    Sheets("sotto_scorta").Cells(2, 1).Resize(10000, 12).ClearContents
    With Sheets("articoli")
        For i = 6 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If .Cells(i, "E") <= .Cells(i, "J") Then
                .Rows(i).Copy Sheets("sotto_scorta").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).EntireRow
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

[john_cash]

Ops kaper
i changed here

.Rows(i).Copy Sheets("sotto_scorta").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).EntireRow

[john_cash]

Thanks also to mohadin

[john_cash]

I tried the kaper macro in another workbook.
must copy the non-blank rows of column B

[Kaper]

Try:

  For i = 6 to .Cells(.Rows.Count, "A").End(xlUp).Row 
    If .Cells(i, "E") <= .Cells(i, "J") And Len(.Cells(i, "B")) > 0 Then
      .Rows(i).Copy Sheets("sotto_scorta").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).EntireRow
    End If
  Next i

Non-empty part is marked with red. I also changed the sequence of copyint to top=to bottom one (blue) as you are not moving, but copying rows. (For moving bottom to top was easier to implement.)

[john_cash]

Hi kaper the macro works almost fine.
I linked the macro to a button and clicking on the button
the rows in the sheet "sotto_scorta" are repeated.
Another thing:
it is possible that the rows are brought into the sheet "sotto_scorta"
without colors, formatting

[john_cash]

I solved it by adding macros created with the recorder:

Option Explicit


Sub passare_a_sotto_scorta()

Dim i As Long

Application.ScreenUpdating = False

Call elimina_righe

With Sheets("articoli")

    
   For i = 6 To .Cells(.Rows.Count, "A").End(xlUp).row
    If .Cells(i, "E") <= .Cells(i, "J") And Len(.Cells(i, "B")) > 0 Then
      .Rows(i).Copy Sheets("sotto_scorta").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).EntireRow
    End If
  Next i
  
End With

Call togli_colore
Call togli_formattazione

Application.ScreenUpdating = True

End Sub



Sub elimina_righe()

    Rows("4:200").Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Select
    
End Sub


Sub togli_colore()

    Rows("4:200").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A4").Select
    
End Sub



Sub togli_formattazione()

    Cells.FormatConditions.Delete
    
End Sub