vba to format several columns with the date format

[dpaton05]

I have some code, trying to format several columns as dates, what is wrong with it as it won't work?

    Set sht = Worksheets("Home")

    With sht

        Set tbl = .ListObjects("tblCosting")
        
        For Each tblrow In tbl.ListRows
            Combo = Format(tblrow.Range.Cells(1, 1), "mmmm yyyy")
            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
                With .Range("A" & Rows.Count).End(xlUp).Offset(1)
                    .PasteSpecial xlPasteValuesAndNumberFormats
[COLOR=#ff0000]                    .Columns("A", "AD", "AE").NumberFormat = "dd/mm/yyyy"[/COLOR]
                '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

 

[MARK858]

Maybe....

    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

        .Range("A:A", "AD:AD", "AE:AE").NumberFormat = "dd/mm/yyyy"
        '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

 

[Michael M]

firstly your columns line is in the wrong place as you're with statement is for column "A"
So, move move it up above that line and use

Range("A1,AD1,AE1").EntireColumn.NumberFormat = "dd/mm/yyyy"

 

[MARK858]

Going mad and left in the quotes in my last post so again maybe...

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

        .Range("A:A, AD:AD, AE:AE").NumberFormat = "dd/mm/yyyy"
        '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