Excel - A Macro to populate data based on criteria

June 2017



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


Published by aakai1056. Latest update on September 12, 2012 at 06:40 AM by deri58.
This document, titled "Excel - A Macro to populate data based on criteria," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).