Copy to another workbook with condition

Question

I have code that copies filtered range from one workbook to another. Code does the work but I would like some improvements. Destination workbook has 12 worksheets named after months (Jan, Feb, Mar, ...). Every day I have to copy filtered range from my main workbook to destination workbook. The problem here comes at the beginning of each month because I have to manually change name of the month in the existing code for data to be copied to appropriate worksheet. Is there any way to automate this? Here is my code: /code/ Sub Z_Report()

   Dim Rng As Range
   Dim wsSource As Worksheet
   Dim wsTarget As Worksheet
   Dim lLastRow As Long
   Dim wbTarget As Workbook
   

Sheets("POS").Activate Range("E4:G4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<>"

   Set wsSource = ThisWorkbook.Sheets("BILLS")
 If bIsBookOpen_RB("Archive.xls") Then
       Set wbTarget = Workbooks("Archive.xls")
   Else
       Set wbTarget = Workbooks.Open("C:Archive_BillsArchive.xls")
   End If
     Set wsTarget = wbTarget.Sheets("May")' this is where I need to change name of the month
   lLastRow = wsTarget.Cells(Rows.Count, "A").End(xlUp).Row
     Set Rng = wsSource.AutoFilter.Range
    Rng.Offset(0, 0).Copy _
   Destination:=wsTarget.Range("A" %26 lLastRow + 1)
   ThisWorkbook.Sheets("BILLS").Activate
   Selection.AutoFilter
   wbTarget.Close Savechanges:=True 

/code/ I'd appreciate any suggestion. Dario

Answer

Dario,

If you will always be copying to the sheet named after the current month, then you can use the current date to get the current month

dim sMonth as String sMonth = Format(date,"mmm")

Set wsTarget = wbTarget.Sheets(sMonth)