Converting Column To Specific Date Format

[georgiboy]

I think I appreciate how your code is working, but not how the rng = var final line?

Just to explain:

The below part sets the range to work on:

With Worksheets("Involved")
     Set rng = .Range(.Cells(2, "D"), .Cells(Rows.Count, "D").End(xlUp))
End With

This part writes the values in that range to an array named 'var':

var = rng.Value

This part loops through that array and converts the text to a UK date format (within the array only):

For x = 1 To UBound(var)
    var(x, 1) = Int(CDate(var(x, 1)))
Next x

As the conversion was made only within the array (in memory) the values need to be written back to the worksheet, the below line does this:

rng = var

To work with a range of spreadsheets, you can use:

Sub test1()
    Dim rng As Range, var As Variant, x As Long, ws As Worksheet
    
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        With ws
            Set rng = .Range(.Cells(2, "D"), .Cells(Rows.Count, "D").End(xlUp))
        End With
        
        var = rng.Value
        
        For x = 1 To UBound(var)
            var(x, 1) = Int(CDate(var(x, 1)))
        Next x
        
        rng = var
    Next ws
End Sub

To work with all worksheets in a workbook, you can use:

Sub test2()
    Dim rng As Range, var As Variant, x As Long, ws As Worksheet
    
    For Each ws In Sheets
        With ws
            Set rng = .Range(.Cells(2, "D"), .Cells(Rows.Count, "D").End(xlUp))
        End With
        
        var = rng.Value
        
        For x = 1 To UBound(var)
            var(x, 1) = Int(CDate(var(x, 1)))
        Next x
        
        rng = var
    Next ws
End Sub