Hi guys,


I will make my issue as quick and concise as possible. I have the following code:

Range("A18:Q36000").Select 
Selection.ClearContents 
Range("A17:Q17").Select 
Selection.AutoFill Destination:=Range("A17:Q500"), Type:=xlFillDefault 
Range("J1").Select
As you can see I have currently set the range to Q500. I would need the autofill destination to be dynamic, depending on what is entered in the cell marked "Tenure" in Excel. For example if the tenure cell value is 10, I want the range to be: Range("A17:Q[Tenure*12+15]), so Range("A17:Q135"). To help, I had the previous copy paste code that worked but was too slow:

Range("A18:Q36000").Select 
Selection.ClearContents 
For i = 1 To Range("tenure") * 12 - 2 
    Range("A17:Q17").Copy 
    Cells(17 + i, 1).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
Next

How do I specify that range with autofill? Any assistance appreciated,


thank you,


Jurij