VBA Loop Until Help

[Samantha Roberts]

I have data on one spreadsheet which gets added to each month 'Raw Data'.

On a different tab within the same workbook 'Formatted Data' - i have formulae in row 2, from column A-O. Note: the formulae is different in each cell. It pulls different data from the 'Raw Data' based on criteria in column A of 'Raw Data'.

At the moment - every time 'Raw Data' is added to, i have to go to 'Formatted Data' and copy the formulae down until it picks up the last row of 'Raw Data'.

I have writen the following to autofill rather than having to do it manually but id like to not have the range "2:2500" defined.

Instead, for it to autofill until the result is 0 (i.e. it's picked up all the rows from 'Raw Data'.


Sub copy()

With Worksheets("Formatted Data")


Range("2:2").AutoFill .Range("2:2500")

End With


End Sub

 

[VoG]

Try

Sub copy()
Dim LR As Long
LR = Sheets("Raw Data").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

With Worksheets("Formatted Data")


    .Range("2:2").AutoFill .Range("2:" & LR)

End With


End Sub

 

[TMShucks]

One way:

Sub copy()
Dim lLR As Long
With Worksheets("Raw Data")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Worksheets("Formatted Data")
    .Range("2:2").AutoFill .Range("2:" & lLR)
End With
End Sub