Run-Time error 1004 when trying to run Macro

[Matt2014]

Hi all, I'm getting the error 1004 whilst running this macro. It was working fine earlier, any advice would be much appreciated.

Sub DateFormat()

' converts dates from dd.mm.yy format to dd/mm/yy format

Dim LastRow As Integer

 jLastRow = Sheets("Data").UsedRange.Rows.Count
 
    Columns("J:J").Select
    
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=REPLACE(RC[-1],3,1,""/"")"
    Selection.AutoFill Destination:=Range(Cells(1, 10), Cells(jLastRow, 10))
    
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=REPLACE(RC[-1],6,1,""/"")"
    Selection.AutoFill Destination:=Range(Cells(1, 11), Cells(jLastRow, 11))

Call GetDays

End Sub

The error occurs on the line: "Selection.AutoFill Destination:=Range(Cells(1, 10), Cells(jLastRow, 10))"




Thanks,

Matt

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Matt2014]

Apologies done now

[Marc L]

Hi,

the selected cell (bad way, better without using any Select in code …)
must be the fisrt cell in the AutoFill range …

[Izandol]

I think it is easier not to autofill:

    Range("J1:J" & jLastRow).FormulaR1C1 = "=REPLACE(RC[-1],3,1,""/"")"
    Range("K1:K" & jLastRow).FormulaR1C1 = "=REPLACE(RC[-1],6,1,""/"")"

[Matt2014]

Hi Izandol,

Thanks for your advice. I'm new to all this so is what you've given me is that to replace the "Selection.AutoFill Destination:=Range(Cells(1, 10), Cells(jLastRow, 10))"

Thanks,

Matt

[Izandol]

This code will replace these lines:

    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=REPLACE(RC[-1],3,1,""/"")"
    Selection.AutoFill Destination:=Range(Cells(1, 10), Cells(jLastRow, 10))
    
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=REPLACE(RC[-1],6,1,""/"")"
    Selection.AutoFill Destination:=Range(Cells(1, 11), Cells(jLastRow, 11))