Comparing cell values and moving whole rows

[comppi]

I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.
In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.

I've tried making my own macros from other examples, but haven't really found anything that would suite my needs and fulfill the objective. I would be so thankful for any help provided! I understand that this might seem really complicated since I'm not that good at phrasing my problem .

Original view of sheet 1:
[TABLE="width: 818"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SUPPLIERSCODE[/TD]
[TD]ORDERAMOUNT[/TD]
[TD]PRODUCTNAME[/TD]
[TD]BARCODE[/TD]
[TD][/TD]
[TD]SUPPLIERSCODE[/TD]
[TD]WAREHOUSE STOCK[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2,00[/TD]
[TD]TEST PRODUCT 1[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1234[/TD]
[TD]2,00[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]4,00[/TD]
[TD]TEST PRODUCT 2[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD]3,00[/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 3[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1236[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 4[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1237[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]1238[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 5[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1238[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]1238[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 5[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The macro would first make the sheet look like:
[TABLE="width: 818"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SUPPLIERSCODE[/TD]
[TD]ORDERAMOUNT[/TD]
[TD]PRODUCTNAME[/TD]
[TD]BARCODE[/TD]
[TD][/TD]
[TD]SUPPLIERSCODE[/TD]
[TD]WAREHOUSE STOCK[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2,00[/TD]
[TD]TEST PRODUCT 1[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1234[/TD]
[TD]2,00[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]4,00[/TD]
[TD]TEST PRODUCT 2[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD]3,00[/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 3[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1236[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 4[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1237[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]1238[/TD]
[TD]2,00[/TD]
[TD]TEST PRODUCT 5[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1238[/TD]
[TD]1,00
[/TD]
[/TR]
</tbody>[/TABLE]

And then cut out the rows in which G<B, so the sheet 1 would look like:
[TABLE="width: 818"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SUPPLIERSCODE[/TD]
[TD]ORDERAMOUNT[/TD]
[TD]PRODUCTNAME[/TD]
[TD]BARCODE[/TD]
[TD][/TD]
[TD]SUPPLIERSCODE[/TD]
[TD]WAREHOUSE STOCK[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2,00[/TD]
[TD]TEST PRODUCT 1[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1234[/TD]
[TD]2,00[/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 3[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1236[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]1,00[/TD]
[TD]TEST PRODUCT 4[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1237[/TD]
[TD]1,00[/TD]
[/TR]
</tbody>[/TABLE]

And Sheet 2:
[TABLE="width: 818"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]1235[/TD]
[TD]4,00[/TD]
[TD]TEST PRODUCT 2[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD]3,00[/TD]
[/TR]
[TR]
[TD]1238[/TD]
[TD]2,00[/TD]
[TD]TEST PRODUCT 5[/TD]
[TD]9999999[/TD]
[TD][/TD]
[TD]1238[/TD]
[TD]1,00[/TD]
[/TR]
</tbody>[/TABLE]

 

[comppi]

Yes, that is correct. If the warehouse stock is lower than the order quantity, then move the whole row to the second sheet. You are perhaps the most helpful person I've ever met on any forum. Cheers!

 

[saltkev]

Hi

Put this under your button 2

Kev

Application.ScreenUpdating = False

Static fx, Lr, LrRange, WS1, WS2, RowHolder
Set WS1 = Sheets("sheet1") ' Alter Sheetname if Required
Set WS2 = Sheets("sheet2") ' Alter Sheetname if Required
WS1.Activate
Lr = ActiveSheet.Range("a70000").End(xlUp).Row
      
LrRange = ActiveSheet.Range("a2:a" & Lr).Address(False, False)

For Each fx In ActiveSheet.Range(LrRange)
        If fx.Value > "" Then
        
        RowHolder = fx.Address(0, 0)
        
        
           If fx.Offset(0, 1).Value = fx.Offset(0, 6).Value Then
            
                    'Do Nothing
            ElseIf fx.Offset(0, 1).Value > fx.Offset(0, 6).Value Then
            
                   fx.EntireRow.Cut
                   
                    
                    WS2.Activate
                    
                             If ActiveSheet.Range("a2").Value = "" Then
                             
                                ActiveSheet.Range("a2").Select
                                ActiveSheet.Paste
                                WS1.Activate
                                Range(RowHolder).Select
                                ActiveCell.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                                GoTo ender
                                
                                
                             ElseIf ActiveSheet.Range("a2").Value > "" And ActiveSheet.Range("a3").Value = "" Then
                             
                                ActiveSheet.Range("a3").Select
                                ActiveSheet.Paste
                                WS1.Activate
                                Range(RowHolder).Select
                                ActiveCell.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                                GoTo ender
                                                          
                                                          
                             ElseIf ActiveSheet.Range("a2").Value > "" And ActiveSheet.Range("a3").Value > "" Then
                             
                                ActiveSheet.Range("a2").Select
                                Selection.End(xlDown).Select
                                ActiveCell.Offset(1, 0).Select
                                ActiveSheet.Paste
                                WS1.Activate
                                Range(RowHolder).Select
                                ActiveCell.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                                GoTo ender
                             End If
             End If
        End If
ender:
WS1.Activate

Next fx

 

[comppi]

You can't even imagine how happy I am right now!! You are awesome. Thank you!

 

[saltkev]

My Pleasure

Kev