Auto Fill Next Column VBA
<p>I have some code here that works, but I'm having some troubles getting it to work multiple times in a row. What it does is it fills the first empty column with a formula from a different sheet.</p>

<pre><code>Dim source As Worksheet
Dim destination As Worksheet
Dim EmptyColumn As Long
Dim LastRow As Long

Set source = Sheets("vlookup")
Set destination = Sheets("COMMIT")

LastColumn = destination.Cells(1,destination.Columns.Count).End(xlToLeft).Column
LastRow = Worksheets("COMMIT").Range("A:A").Rows.Count

If IsEmpty(destination.Range("A2")) = False Then
EmptyColumn = LastColumn + 1
destination.Cells(3, EmptyColumn).Formula = "=INDEX(PORT!$S$5:$S$4000,MATCH(COMMIT!$G3,PORT!$G$5:$G$4000,0))"
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("AL3").AutoFill destination:=Range("AL3:AL" &amp; LastRow) **'This is where I'm having issues'**
End If
</code></pre>

<p>I would like it to keep putting the index formula into the next empty column, but I only know how to put it into a range that I have set and will not move to the next column when I run this macro again.</p>

<p>Any thoughts on how I could achieve this?</p>

<p>Thanks to Scott for helping me out with this!</p>

<p>Now I have another question that has popped up in my code.</p>

<pre><code>Set cellSource = Worksheets("COMMIT").Range(Cells(1, LastColumn).Address)
Set cellTarget = Worksheets("COMMIT").Range(Cells(1, LastColumn), Cells(1, EmptyColumn))
If detntn.Range("A2") &lt;&gt; "" Then
cellSource.AutoFill destination:=cellTarget, Type:=xlFillDefault
End If
</code></pre>

<p>Now I'm trying to copy a formula from the "Commit" worksheet and paste it in the first cell of the first empty column. For some reason this code is not working. Wondering if anyone can sniff out as to why. Thanks in advance for your help!</p>