VBA code to copy data to a new sheet base on the current year

[TRY369]

Hi there so I have this problem whereby I am not able to copy a row of data from sheet(general_logsheet) to the new sheet. I only want to paste the data that has the current year in it onto a new sheet. The catch is that every year the new sheet will be created named as (year(date) & "_logged"). Example: 2020_Logged, 2021_Logged.

Private Sub LoggedAss_Click()
Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
Dim currentDate As Date

lastrow = Sheets("General_LogSheet").Cells(Rows.Count, 1).End(xlUp).Row
currentDate = Year(Date)

With Sheets("General_LogSheet")
  .Select
  .Range("A1").Select
End With

For i = 2 To lastrow

    mydate = Cells(i, 4)
    If mydate = currentDate Then
    erow = Sheets(Year(Date) & "_logged").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Range(Cells(i, 1), Cells(i, 4)).COpy Destination:=Sheets(Year(Date) & "_logged").Cells(erow, 1)
    End If
    Next i

End Sub

 

[vcoolio]

Hello Try,

You could try this as a starting point:-

Sub Test()
       
        Dim stName As String
        stName = Format([Today()], "YYYY")
        Dim wsGL As Worksheet: Set wsGL = Sheets("General_LogSheet")
        Dim wsD As Worksheet: Set wsD = Sheets(stName & "_Logged")

Application.ScreenUpdating = False

        wsD.UsedRange.Offset(1).Clear
       
        With wsGL.[A1].CurrentRegion
                .AutoFilter 4, 13, 11
                .Offset(1).EntireRow.Copy wsD.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
        wsGL.[A1].AutoFilter

Application.ScreenUpdating = True

End Sub

I'm not sure if the destination worksheet is in table format (which is not necessary BTW) but I'm sure you'll let us know.

I hope that this helps.

Cheerio,
vcoolio.

 

[TRY369]

Hi there! Thanks for your help, the code works wonderfully as per what I needed. Just for curiosity though, what is this line? Especially the auto-filter part with the numbers.

With wsGL.[A1].CurrentRegion
                .AutoFilter 4, 13, 11
                .Offset(1).EntireRow.Copy wsD.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With

 

[vcoolio]

Hello Try,

I'm glad that it's all working for you.

Just a brief explanation of the autofilter line:-
- 4 represents Column D which is the date column on which the filter is set.
- 13 is the Excel enumeration for xlFilterThisYear.
- 11 is the Excel enumeration for XlFilterDynamic.

Cheerio,
vcoolio.