VBA- Auto-fill down based on another sheet last row not working

[paulcherianc]

I have used the following code to Auto fill down the formula on another sheet last used row. While running macro for the first time it's working well and if I do it again it doenst work. Please help!!

Sub MaindataAutoFill()
  
    Dim Rng As Long, Rng1 As Range
    Rng = Sheets("Timesheet").Range("A" & Rows.Count).End(xlUp).Row ' the range of entries
    With Sheets("Main Data")
        .Range("A8:AH" & Rng).FillDown ' the range where the formula is in the first cell
    End With
  
  
End Sub

I used the following code to clear the contents in order to rerun the AutoFill:

Sub ClearMainData()
   
Worksheets("Main Data").Activate
    Range(Range("A8"), Cells(Rows.Count, Columns.Count)).ClearContents
End Sub

 

[NARAYANK991]

Hi ,

If row #8 is the row which has formulae , you cannot clear its contents.

If row #7 is the row which has formulae , then the fill down should include this row , and your line of code should be :

.Range("A7:AH" & Rng).FillDown ' the range where the formula is in the first cell

Narayan