Excel - A Macro to populate data based on criteria

Ask a question


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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team