VBA code in my command button doesn't work [Solved]

Ask a question ips999 - Last answered on May 28, 2017 at 08:06 PM by vcoolio

I have a sample table below, I was trying different VBA code in my command button but doesn't work well.
Column G is already programmed to indicate reorder base on Column F. I want to copy all rows with Status "Reorder" to sheet 2. And in Sheet 2 i want to show only columns A to E and when I go back to Sheet 1 and add more Items to count and audit again it will be added again to sheet 2. I have inserted a command button so only the correct code is what I need to make it work.

Item#Name Desc Price Order Quantity Status Reorder Level QuantityinReorder
101 Apple Red £2.00 2 3 Reorder 3 5
102 Pear Gre £3.00 0 6 3 6
103 Bana Yello £4.00 0 7 4 7
104 Oran Oran £5.00 5 3 Reorder 4 8
105 Man Oran £2.00 4 5 Reorder 5 9
106 Grap Purpl £1.00 6 4 Reorder 5 10
107 Carro Oran £1.00 4 8 6 12
108 Potat Yello £2.00 9 11 10 20
109 Peas Gree £1.00 8 12 10 20
110 Onio Viole £1.00 1 3 2 4

Thank you very much!

plus moins
Hello Ips999,

Try the following code, assigned to the button:-

Sub TransferData()

Dim lr As Long

Application.ScreenUpdating = False

Sheet1.Range("G1", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Reorder", 7
  lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If lr > 1 Then
      Sheet1.Range("A2", Sheet1.Range("E" & Sheet1.Rows.Count).End(xlUp)).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
          'Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)).EntireRow.Delete
         End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

If the Status column is Column G, then the above code will filter that column for the criteria "Reorder" and then transfer the relevant row of data (just from Columns A - E) to sheet2.

If you wish to clear the "used" data from sheet1 that has been transferred to sheet2, then just remove the apostrophe(') from the begining of line 14 in the code above (the line in green font).

I hope that this helps.

Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!