Hello everyone,
I'm trying to find an easier way to copy a bunch of information. First, I need to determine any numbers in row L (YTD Expenses) that are negative. Second, I need to determine what Grant those negatives are on (Row C, Grant). I then need to copy ALL the rows that contain that grant number to a different tab. I have been doing this manually but surely there is a better method.
Any help is greatly appreciated.
Thanks in advance!!
Ryan
Re: Copying rows based on specific criteria
hi Twilo023,
why not use a filter for values <0
Re: Copying rows based on specific criteria
Good Morning Pike,
That's what I currently do. I filter for values <0 to determine what grants contain those lines. I will then un-filter the values and do a filter for all the grants containing negative lines. While its not super tedious.. I guess I was hoping for a formula or macro to help expedite the process. That maybe more effort than keeping the present method.. just thought I'd see if anyone had ideas for a different approach.
Thanks!
Re: Copying rows based on specific criteria
Ok.. maybe record a macro of your steps and run that macro?
Re: Copying rows based on specific criteria
Would recording a macro work? The grants with negatives lines changes all the time..
Re: Copying rows based on specific criteria
Hello Ryan
Does your final output data include positive values from column L - i.e. you search for all negative values in column L first and then search again for all rows with grant numbers that you identified in the initial search?
Re: Copying rows based on specific criteria
Hi Dangle - Yes, the final output data contains both positive and negative numbers.
Re: Copying rows based on specific criteria
Does this work for you?
Sub Filtering()
Dim a, i As Long, j As Long, z
Dim aList As Object
Set aList = CreateObject("System.Collections.ArrayList")
a = Sheets("Sheet1").Cells(1).CurrentRegion.Value
ReDim z(1 To UBound(a, 2))
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a, 1)
If a(i, 12) < 0 Then .Item(a(i, 3)) = .Count + 1
Next
For i = 1 To UBound(a, 1)
If .exists(a(i, 3)) Then
For j = 1 To UBound(a, 2)
z(j) = a(i, j)
Next
aList.Add z
End If
Next
End With
With Sheets("Sheet2")
Sheets("Sheet1").Rows(1).Copy .Cells(1)
.Cells(2, 1).Resize(aList.Count, UBound(a, 2)).Value = Application.Transpose(Application.Transpose(aList.toarray()))
End With
End SubRe: Copying rows based on specific criteria
Hi Dangle - It wasn't working at first but I figured out I need to change the sheet names in your code (they are changed in my version because of the export). Seems to be working now. I'll keep testing and let you know for sure.
Muchas Gracias!!
Re: Copying rows based on specific criteria
No problem and glad you got it working. Let me know if it needs adjusting.
Don’t have an account yet? Register yourself now and be a part of our community!