SOLVED

VBA Code - Autofill Down - Copying two cells down to the end of the last row

New Contributor

Need help correcting a Macro that will automatically copy data in two cells down to the last row.  I have attempted to use the other conversations similar to this to fix error I am receiving but I continue to get errors.  Here's the code I have now:

 

Sub CopyDownAA_AB() ' ' CopyDownAA_AB Macro ' '

     Range("AA2:AB2").Select

     Selection.AutoFill Destination:=Range("AA2:AB105")

     Range("AA2:AB105").Select

     Range("AA1").Select

End Sub

 

From:

JT_FrankGroup_0-1660148032860.png

 

TO:

JT_FrankGroup_0-1660148154445.png

 

4 Replies

@JT_FrankGroup 

The code looks OK (although it is not necessary to select ranges). What is the error message that you receive?

@Hans Vogelaar Thanks for getting back with me!  Each sheet has a different number of rows.  So when I use the macro on another sheet, it only goes down to Row 105, even if I have 200 rows.  I need it to copy down to the last row, regardless of how many rows there are.

best response confirmed by JT_FrankGroup (New Contributor)
Solution

@JT_FrankGroup 

Try this version:

Sub CopyDownAA_AB()
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("AA2:AB2").AutoFill Destination:=Range("AA2:AB" & LastRow)
End Sub