Reference last row of different sheet

Question

currently I have this on Sheet2

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A1001")
Range("A2:A1001").Select
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B1001")
Range("B2:B1001").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1001")
Range("C2:C1001").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D1001")
Range("D2:D1001").Select
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E1001")
Range("E2:E1001").Select
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G1001")
Range("G2:G1001").Select
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H1001")
Range("H2:H1001").Select
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I1001")
Range("I2:I1001").Select
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J1001")
Range("J2:J1001").Select
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K1001")
Range("K2:K1001").Select

Instead of having the destination range A2 to A1001 I want to have A2 to the same last row as sheet1 e.g if in sheet1 the last row is row 147 I want the code to fill down Selection.AutoFill Destination:=Range("A2:A147")

I do not know how to accomplish this.

Thanks

Google how to find last row. Edit: I only say this because in the time it takes to write your question, google would already told you ^_^; — findwindow, May 16 '16 at 20:57
[How to find the last row](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) since I have it bookmarked. — gtwebb, May 16 '16 at 21:06
`With Sheets("Raw") lastrow = .Range("I" & .Rows.Count).End(xlUp).Row End With With Sheets("Fixer") Range("A2").Select Selection.AutoFill Destination:=Range("A2:A&lastrow") Range("A2").Select` Like that???? — Tom Ruiz, May 16 '16 at 21:12

score 2 · Accepted Answer · answered May 16 '16 at 22:45

This should work for you...

Sub LastRowAutofill()
    On Error Resume Next
    Dim wsSheet1 As Worksheet: Set wsSheet1 = Worksheets("Sheet1")
    Dim LastRow As Long
    LastRow = wsSheet1.Columns(1).Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row

    Dim i As Long
    With Worksheets("Sheet2")
        For i = 1 To 11
            If i <> 6 Then .Cells(2, i).AutoFill Destination:=.Range(.Cells(2, i), .Cells(LastRow, i))
        Next i
    End With

End Sub

Or you can use the following if you want the last row in the entirety of sheet1:

    LastRow = wsSheet1.UsedRange.Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row

score 0 · Answer 2 · answered May 16 '16 at 22:37

Assuming your original data is on Sheet1 and you are copying to Sheet2, here's how you would do it:

Dim intLastrow As Integer

intLastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Range("A2").Select
Selection.AutoFill Destination:=Range(cells(2,"A"),cells(intLastrow, "A"))
Sheets("Sheet2").Range(Cells(2, "A"), Cells(intLastrow, "A")).Select

intLastrow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row    
Sheets("Sheet2").Range("B2").Select
Selection.AutoFill Destination:=Range(cells(2,"B"),cells(intLastrow, "B"))
Sheets("Sheet2").Range(Cells(2, "B"), Cells(intLastrow, "B")).Select

Reference last row of different sheet

2 Answers2