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
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)