Hello Team,
I am trying to get the first and last row with a VBA code where it filter the list based on a code and currency, then i would like the VBA code to copy the first row and last of filtered data.
The issue am getting with the below code is that it copies the header when there are no transaction but a code has been referenced in the account number sheet. The account number sheet is a kind of a permanent list of bank account number and currency and every month we have transaction and some accounts may not have also.
Can you also please help add the first row code as I could not figure it out.
Thank you
Sub FilterData()
Application.ScreenUpdating = False
Dim LastRow As Long, acSheet As Worksheet, srcWS As Worksheet, desWS As Worksheet, code As Range
Set acSheet = Sheets("Account Number")
Set srcWS = Sheets("Summary")
Set desWS = Sheets("Ending Balance")
LastRow = acSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each code In acSheet.Range("A4:A" & LastRow)
With srcWS.Cells(1).CurrentRegion
.AutoFilter 1, code
.AutoFilter 2, code.Offset(, 1)
srcWS.Range("A" & Rows.Count).End(xlUp).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
End With
Next code
srcWS.Range("A1").AutoFilter
Application.ScreenUpdating = True
End Sub
Bookmarks