I would like to create a macro that will autofill(increment of 1) certain amount of rows based on a number. For example, in Sheet1, I have 100 random numbers from A1 to CV1. And in Sheet2, I have 100 reference numbers from A1 to CV1. These numbers are the ones I want to be autofill based on the numbers from Sheet1.

Here's a sample of my Workbook.

Sheet1:

A1=1
B1=2
C1=3

Sheet2:

A1=A10--Would autofill down 1 row.
B1=B11--Would autofill down 2 rows.
C1=C12--Would autofill down 3 rows.

And the end result would be:

A1=A10, A2=A11
B1=B11, B2=B12, B3=B13
C1=C12, C2=C13, C3=C14, C4=15


Thank you for your help.

You are working with 2 spreadsheets.

Sheet1 you are saying how many rows under row 1 should be auto-filled for formula?

Sheet2 Has the initial formula set. e.g. A1 has the formula '=A10'?

Then you want the autofill to be content on sheet 2?

That is how I read it. The autofill code as I understand it would be:

Assuming you wish to assign this functionality to a button, the code for the button would be something like:

----------------------------------------------------------------------
'Autofills based on values in Source Sheet
  Dim oTarget As Worksheet, oSource As Worksheet, iColumn As Integer
  Set oTarget = ActiveWorkbook.Worksheets("Target")
  Set oSource = ActiveWorkbook.Worksheets("Source")
  iColumn = 1
  If oSource.Cells(1, iColumn).Value = "" Then Exit Sub
  Do While oSource.Cells(1, iColumn).Value <> ""
    oTarget.Range(oTarget.Cells(1, iColumn), oTarget.Cells(oSource.Cells(1, iColumn).Value + 1, iColumn)).Value = oTarget.Cells(1, iColumn).Formula
    iColumn = iColumn + 1
  Loop

End Sub
----------------------------------------------------------------------
I hope this helps.

Thank you for the reply, Allenm. It was what I was looking for. But is there a way to increment by 1 on the "target" sheet. So if source sheet has data 2 in cell a1, it would increment by 1 on the target sheet.

Example
Target sheet would be A1=a10, A2=A11, A3=A12.

Thank you for the help again.

If I'm not mistaken, if you change A1 to the value of 2 on the source sheet and run the code it should have the values you specify for A1, A2, and A3 on the target sheet already.

The way it works:
I'm using the auto populating feature of Excel itself by setting a range equal to one cell. Since the formula starts in A1 we include it as part of the range being set to.
the range (simplified for readability) is:

Range(Cells(1,Y), Cells(X+1, Y))
Basically the range encapsulates all cells from Row 1 Column Y to Row X+1 Column Y where X is the value entered in the cell on the source page and Y is the column number on.

To make the range equal the formula we need to set Range().Value = Cells().Formula.

Thus the range above is set equal to the initial value in the first cell:

Cells(1, Y).Formula
Where Y is the Column number on.

Does this help explain it?

If this isn't what you mean by having the value to increment by 1 on the target sheet then I'll need more specifics to answer the question.