PDA

View Full Version : Solved: VBA AUTO FILTER



rahul_r79
01-21-2010, 08:48 PM
Hi
I am just trying to write a macro ,so that i can select a date from the file and filter the data and then copy and then paste it to the new sheet.But
when i run this macro it is not copying the data in the new sheet. Can anyone please help:banghead:

markmrw
01-22-2010, 05:22 AM
the date that was being selected was in feb (next month) so it was filtering everything out, as nothing matched your criteria

rahul_r79
01-22-2010, 04:44 PM
Hi
But there are contracts which are maturing before next month and I want to select all the contract before the current date + 31 days ,that is why i had put less than and Equal to in the condition:help

lucas
01-22-2010, 07:52 PM
It seems to work for me. Not sure of the objective but it definately copies data to a new sheet when I run it.

see attached.

rahul_r79
01-24-2010, 03:26 PM
hi
But When I am running it , it doesnot show any data copied in to the new sheet,Do you think is there any problem with my default settings

lucas
01-24-2010, 03:33 PM
When you click the button on the workbook I posted does it not create a new sheet and add data to it?

rahul_r79
01-24-2010, 04:17 PM
It creates the workbook but still data is not there, Is am doing somethin very wrong or stupid

geekgirlau
01-24-2010, 08:56 PM
Excel is very US-centric when it comes to dates

Change
Selection.AutoFilter Field:=5, Criteria1:="<=" & dDate
to
Selection.AutoFilter Field:=5, Criteria1:="<=" & Format(dDate, "mm/dd/yyyy")

rahul_r79
01-24-2010, 09:15 PM
U are awesome.......Genius..Thanks A Million

geekgirlau
01-24-2010, 10:13 PM
My pleasure. Don't forget to mark the thread as "Solved" using Thread Tools at the top of the page.

rahul_r79
01-26-2010, 05:10 PM
Hi
If i want to delete the copied rows from the main sheet, I am trying to put
the line like Selection.EntireRow.Delete. it is not deleting the copied rows from the main sheet.

geekgirlau
01-26-2010, 06:59 PM
I'd be a little careful with this, as you're adding a degree of complexity.

Currently you're creating a new sheet and copying the data across. The code does not cope if the "Maturity" sheet already exists, so you're not appending new records after the initial copy.

What about just using the filter? I would suggest:

Add a column containing the formula to indicate those records that have reached "maturity". The formula is pretty simple - "=E2<=$T$1".
You can then use the standard autofilter to give you different "views" of the data: records that have reached maturity, records that have not reached maturity or all records.
If required, you could also have a field to calculate either the number of days past maturity, or the number of days remaining until maturity, or both.Obviously this depends on your overall objectives, but I don't know that there is a benefit in having to constantly copy and then remove data from your main list.

rahul_r79
01-26-2010, 10:55 PM
Hi I am getting a datafile everyday and from that i have to send multiple reports to different area ,I am trying to make an excel template so that i can paste the data and by running few macro , the reports will be generated and save lot of time that why I updated the previous macro also and used cut and paste method
Dim dDate As Date
Dim counter As Long
Dim RowCount As Long
Dim l As Date
l = Range("T1").Value
dDate = l
Application.ScreenUpdating = False
RowCount = Range("E65536").End(xlUp).Row
counter = 1
Do Until counter > RowCount
If Range("E" & counter).Value <= dDate Then
Range("E" & counter).EntireRow.Cut Destination:=Sheets("Sheet3"). _
Range("A65536").End(xlUp).Offset(1, 0)
Range("E" & counter).EntireRow.Delete
RowCount = RowCount - 1
counter = counter - 1
End If
counter = counter + 1
Loop
Application.ScreenUpdating = True

End Sub

rahul_r79
01-26-2010, 10:58 PM
this is my second macro which will select the data on 2 value ,cut it and then paste in another sheet but is giving error
Dim counter As Long
Dim RowCount As Long

Application.ScreenUpdating = False
RowCount = Range("I65536").End(xlUp).Row
counter = 1
Do Until counter > RowCount
If(AND (Range("I" & counter).Value = 2, Range("L" & counter).Value = 4)) Then ' this line is giving an error
Range("I" & counter).EntireRow.Cut Destination:=Sheets("Sheet2"). _
Range("A65536").End(xlUp).Offset(1, 0)
Range("I" & counter).EntireRow.Delete
RowCount = RowCount - 1
counter = counter - 1
End If
counter = counter + 1
Loop
Application.ScreenUpdating = True
End Sub

geekgirlau
01-31-2010, 04:55 PM
Rahul, make sure you use the VBA tags when posting code (the little green button with "VBA").


Dim counter As Long
Dim RowCount As Long

Application.ScreenUpdating = False
RowCount = Range("I65536").End(xlUp).Row
counter = 1

Do Until counter > RowCount
If Range("I" & counter).Value = 2 And Range("L" & counter).Value = 4 Then
Range("I" & counter).EntireRow.Cut Destination:=Sheets("Sheet2"). _
Range("A65536").End(xlUp).Offset(1, 0)
Range("I" & counter).EntireRow.Delete
RowCount = RowCount - 1
counter = counter - 1
End If

counter = counter + 1
Loop
Application.ScreenUpdating = True

rahul_r79
01-31-2010, 06:46 PM
You are awesome ,Thanks a Lot for helping me with this.