VBA Autofill horizontally referencing a variable date in a cell

[armchairandy]

I have the below vba to autofill the formula between J17 to ACP17 based on if there is data in column E.

Range("J17:ACP17").AutoFill destination:=Range("J17:ACP" & Range("E" & Rows.Count).End(xlUp).Row)

Works fine, but

J17 to ACP17 hold formulas that relates to a row of sequential dates, which is variable as the length of time constantly changes.

The problem I have is that from J17 to ACP17 there is a lot of unnecessary formulas making the workbook too large (multiple tabs using the same process).

Column E is ok, the data is copied in each time so the formulas downwards works fine.

What I would like to do is have a start date in J16, then autofill to the right up to the end date held in cell B1, then replace the J17:ACP17 section of the above formula with the last date in row 16.

Makes sense?

Any help would be appreciated

Regards

Andrew

 

[DanteAmor]

Try this

Sub test()
  Dim nDate As Long, j As Long
  
  j = 9
  For nDate = [J16] To [B1]
    j = j + 1
    Cells(16, j) = nDate
  Next
    
  Range("J17", Cells(17, j)).AutoFill Range("J17", Cells(Range("E" & Rows.Count).End(xlUp).Row, j))
End Sub

 

[armchairandy]

DanteAmor

Many thanks for your advice - works really well

Really appreciated

Andrew

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.