CODEWITHSUNDEEP
vbaexcel
Tom Ruiz
Tom Ruiz

Reputation: 307

Reference last row of different sheet

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

Upvotes: 0

Views: 5667

Answers (2)

Tragamor
Tragamor

Reputation: 3634

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

Upvotes: 2

pheeper
pheeper

Reputation: 1527

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

Upvotes: 0

Related Questions