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

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:

TO:

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

4 Replies

HansVogelaar
MVP
Aug 10, 2022
JT_FrankGroup

The code looks OK (although it is not necessary to select ranges). What is the error message that you receive?
- JT_FrankGroup
  Copper Contributor
  Aug 10, 2022
  HansVogelaar 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.
  - HansVogelaar
    MVP
    Aug 10, 2022
    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

Forum Discussion

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

4 Replies

Resources