VBA code to specify sheets depending on date

[dpaton05]

I have a spreadsheet that transfers rows from a excel table to sheets depending on the date. The reporting month of where I work goes from the 26th of the previous month to the 25th of the current month. I had it all working when the table in my home sheet had one row but now it has multiple rows. I extracted the month from the current date and checking the date using these functions:

Column W in my table:

=TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "mmmm")

Column X in my table:

=TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "yyyy")

column Y in my table:

=CONCATENATE(W5," ",X5)

With one row, I used the following code to get combo:

 Combo = Worksheets("Home").Range("Y5")

With the columns W, X and Y, the same for each row in the table.


Norie helped me with my code to copy rows to relevant sheets and suggested I just use the date column to extract the name of the monthly sheet to transfer the row to. I forgot about needing to move the rows to months, not just depending on the month, but depending on being between the 25th and 26th.

Sub cmdCopyo()
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim tblrow As ListRow
Dim Combo As String
Dim sht As Worksheet
Dim tbl As ListObject

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

    With sht

        Set tbl = .ListObjects("tblCosting")
        
        
        
        For Each tblrow In tbl.ListRows
           [COLOR=#ff0000] Combo = Format(tblrow.Range.Cells(1, 1), "mmmm yyyy")[/COLOR]
            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

I am not very good at writing vba and this is my copy code above. I need to change the highlighted line so that combo refers to the value in column Y for each row in the table, not just hard coded in the be cell Y5.

Can I have some help please?

 

[JLGWhiz]

This should do it

Combo = Format(tblrow.Range.Cells(1, [COLOR=#ff0000]25[/COLOR]), "mmmm yyyy")

 

[dpaton05]

Thanks

 

[footoo]

Do you need columns W an X ?

Could get column Y data with :
=TEXT(DATE(YEAR(A5),MONTH(A5)+(DAY(A5)>26),1), "mmmm yyyy")

 

[dpaton05]

That's just how I did it. I could change it now but there are too many references to cells in vba to bother doing that.