vba code to copy to specific sheet within a set workbook

[dpaton05]

I had a spreadsheet with monthly sheets in it. Entries from a table are transferred to the relevant sheet depending on the date. I have now just learned that my supervisor wants the table to be in a separate spreadsheet to the entries. I therefore need my copy code to specify the financial year from the date of the entry about to be transferred and put in into the correct document (This is for the Australian financial year, which is July-June). I then need it to run the sort macro on the document where the entry has been put. Here is my code I have. Could someone help me change it so the entry will go into the correct monthly sheet in the correct yearly document please?

Sub cmdCopy()

Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim tblrow As ListRow
Dim Combo As String
Dim sht As Worksheet
Dim tbl As ListObject
Dim lastrow As Long

    Application.ScreenUpdating = False
    
    'assign values to variables
    Set sht = Worksheets("Home")
    
    With sht

        Set tbl = .ListObjects("tblCosting")
        
        
        
        For Each tblrow In tbl.ListRows
            Combo = Format(tblrow.Range.Cells(1, 25), "mmmm yyyy")
            lastrow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo

            Set wsDst = Sheets(Combo)
            
            With wsDst
                'This copies the first 10 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
                tblrow.Range.Resize(, 10).copy
                .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                'This should go to the 15th column in the current row, i.e. column O, and copy that column and the next 2 columns, i.e. O:Q, to column K on the destination sheet.
                tblrow.Range.Offset(, 14).Resize(, 3).copy
                .Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                'Similarly this should copy columns AD:AF from the table to column N on the destination sheet.
                tblrow.Range.Offset(, 29).Resize(, 3).copy
                .Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                
            End With
            
        Next tblrow
        
        Call SortDates
        
    End With
    
    Application.CutCopyMode = False

    Application.ScreenUpdating = True
    
End Sub

I also need code to sort the entries in each month by date. I had some code called SortDates but it doesn't work?


The documents will be open and will be named, for example, "NPSS work allocation sheet 2018 - 2019" or "NPSS work allocation sheet 2025 - 2026" etc.

Thanks,
Dave

 

[dpaton05]

Can someone please help me with this?