 0
Thanks

A few words of thanks would be greatly appreciated.

Excel - A Macro to populate data based on criteria 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.
0
Thanks

A few words of thanks would be greatly appreciated. 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.