Copy the filtered dates, do Formatting and paste it back to filtered column

[Rajkumar_h]

Hello Experts,

I have the dates columns in the sheet name "Dates" where the format of the dates are wrong. I filter the wrong dates to sheet name "Raw Dates" where I have placed formulas that corrects the date format in (col I) and when I'm trying to paste the dates back to same column I am getting debug.

Not sure which is correct VBA code, below is code and also attaching the excel file for your reference.

If you have a better option than this, then would be appreciated.

[/COLOR][COLOR=#333333]Sub DateFormat()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
Dt = Format(DateSerial(Year(Date), Month(Date), 1), "YYYY")
lr = Cells(Rows.Count, 1).End(xlUp).Row

    ActiveSheet.Range("A1:C" & lr).AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "12/9/" & Dt)
    Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("Raw Dates").Range("A2")
    
Sheets("Raw Dates").Select
Lrw = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:I" & Lrw).FillDown
Range("I2:I" & Lrw).Copy

Sheets("Dates").Select
Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).PasteSpecial xlPasteValues 'stuck here
 </code>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

Regards,
Rajkumar H

 

[igold]

Why filter the dates. Why don't you just apply the correct formatting to the entire column, the dates that are already formatted correctly should remain the same, and the incorrectly formatted dates should pick up the correct format.