Excel - A Macro to populate data based on criteria

December 2016



Issue


I would like to populate data from 1 sheet to another sheet based on certain criterias. With reference to the sample data provided in the link below:
  • Populate data into a new sheet called "Slow Moving" when Column H is more than 90 and column D not equal to zero.
  • Populate data into a new sheet called "Non Moving" when Column G is zero and column D not equal to zero.

Solution


Try this macro:

Insert the two sheets "Slow moving" and "Non Movingt". Run this macro (in the macro the worksheets name should be exactly as the sheet names):
Sub test() 
Dim j As Long, k As Long 
undo 
Worksheets("6200_Data").Activate 

k = Range("a6").End(xlDown).Row 
For j = 1 To k 
If Cells(j, "H") > 90 And Cells(j, "D") <> 0 Then Cells(j, "A").EntireRow.Copy _ 
    Worksheets("Slow Moving").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
If Cells(j, "G") = 0 And Cells(j, "D") <> 0 Then Cells(j, "A").EntireRow.Copy _ 
    Worksheets("Non Moving").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
Next j 
Worksheets("Slow Moving").UsedRange.Columns.AutoFit 
End Sub 

Sub undo()  
Worksheets("slow Moving").Cells.Clear 
Worksheets("Non Moving").Cells.Clear 
End Sub 


Thanks to Venkat1926 for this tip.

Related :

This document entitled « Excel - A Macro to populate data based on criteria » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.