you may be a vba newbie, but definitely not a vbaexpress newbie.
please use code tags when posting your code.
for replace and date format, you can use whole column.
Sub vbax_61213_correct_cate_format_sheets_cols()
Dim wbk As Workbook
Dim x As Long
Dim SheetNames As Variant, Cols As Variant
Set wbk = Workbooks.Open(Sheets("Sheet1").Range("B5").Value)
SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
Cols = Array("J:J", "L:L", "P:P", "R:R")
With ActiveWorkbook
For x = LBound(SheetNames) To UBound(SheetNames)
.Worksheets(SheetNames(x)).Columns(Cols(x)).Replace " *", "", xlPart
.Worksheets(SheetNames(x)).Columns(Cols(x)).NumberFormat = "yyyy-mm-dd"
Next
End With
End Sub