VBA code in my command button doesn't workSolved/Closed

Question

Hello, 

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.


A        B      C      D       E       F           G           H                     I
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!


System Configuration: Windows / Chrome 58.0.3029.110

vcoolio · Answer

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
             Sheet2.Columns.AutoFit
         End If
Sheet1.[G1].AutoFilter

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.

Cheerio,
vcoolio.

VBA code in my command button doesn't work

1 response

Similar discussions

Subscribe To Our Newsletter!