Excel: Populate data based on criteria [Solved/Closed]
wliang41
Posts Thursday June 16, 2011Registration date
May 7, 2014
Last seen
-
Jan 13, 2012 at 01:00 AM - Latest reply: venkat19261865
Posts Sunday June 14, 2009Registration dateContributorStatus
July 30, 2015
Last seen
- Jan 31, 2012 at 11:19 PM
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 for your help. The macro is working okay now. However, I noticed that when the macro is executed, the header row and the total were copied to sheet "Slow Moving" but not to sheet "Non Moving".
How can I insert the header row to sheet "Non Moving" and omit the total row from both sheets?
I have uploaded the file and its result for your reference.
Sub test()
Dim j As Long, k As Long
undo
Worksheets("6200_Data").Activate
k = Range("a6").End(xlDown).Row
For j = 6 To k
'MsgBox j
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
With Range("a3").EntireRow.Copy
Worksheets("Slow Moving").Range("A1").PasteSpecial
Worksheets("Non Moving").Range("A1").PasteSpecial
End With
Worksheets("Slow Moving").UsedRange.Columns.AutoFit
Worksheets("Non Moving").UsedRange.Columns.AutoFit
End Sub
Sub undo()
Worksheets("Slow Moving").Cells.Clear
Worksheets("Non Moving").Cells.Clear
End Sub
Hi venkat1926,
Thanks for your reply. I have tried out the macro and it works now.
I still have a few questions that will need your help as I am still doing improvements on the report.
1. I want to have a subtotal of the stock value on the top of the sheet for both Slow Moving and Non Moving. And these values will need to be populated into the Summary sheet. I have recorded a macro to insert the subtotal after the data was splitted into different sheets and reference the subtotal field into the Summary. However, the field in the Summary will not change to the correct value after I ran the macro to insert the subtotal. You can see the result in the attached file.
2. Also, I will need to do a vlookup and would like to incorporate it into the macro. The column for the vlookup will be always the same column. Please advise how put this vlookup function into the macro.
Hi venkat1926,
Thanks for your reply. I have tried out the macro and it returned the results desired. However, there are still some questions from my side that would need your expertise.
1. I tried to have the subtotal from Col C1 from Slow Moving sheet automatically populated into Summary sheet after macro "test" was executed. So, I entered this formula (='Slow Moving'!$C$1) into Summary sheet. However, after macro "test" was executed, the cell was referenced to C3 instead of C1. Please advise how to "fix" the referenced field in Summary sheet to always refer to C1 after macro "test" is executed.
2. I did a vlookup from Slow Moving sheet to 6810 sheet to get the data from Col I into Slow Moving sheet. I recorded a macro for this activity. However, I noticed that if the cursor was not put into the correct position, the data populated will not be correct as the macro was considering the field where the cursor is as the starting point to lookup the data in 6810 sheet.
I have upload my original file below for your reference. By the way, I have not incorporated the changes made from your previous reply into this file.
sorry this morning I am confuse. there are two slow moving and two non moving. which one you are talking about.
ok now in summary sheet the entries in cell B23 and B 31 are same.Had you given the B23 as "6200_Slow_Moving_value" then it would have been easier to write the macro and also make the macro more general . similarly for B24 and B32. whild designing these matters must be kept in view
anyhow I have written a macro which either you can run independentlyl or incorporate the name of this macro in some other macro
Sub summing()
Dim ssum(1 To 4) As Double, r As Range, ws(1 To 4) As Worksheet
Dim cfind As Range
Set ws(1) = Worksheets("6200_Slow_Moving")
Set ws(2) = Worksheets("6200_Non_Moving")
Set ws(3) = Worksheets("6810_Slow_Moving")
Set ws(4) = Worksheets("6810_Non_Moving")
For j = 1 To 4
With ws(j)
ssum(j) = WorksheetFunction.Sum(Range(.Range("C4"), .Range("C4").End(xlDown)))
End With
Next j
With Worksheets("summary")
.Range("C23") = ssum(1)
.Range("C24") = ssum(2)
.Range("C31") = ssum(3)
.Range("C32") = ssum(4)
End With
End Sub
"formula
(='Slow Moving'!$C$1)
is this correct??
should it not be
='Slow Moving'!$B$1
look into itn again.
regarding vlooup better do not incorporate it in a macro first write the macro separately and debug and if it OK incorporate it.
As I am not an expert I find it difficult to write a formula into a macro with variables.
