Macro to fill down all cells from a vlookup on a different sheet

[JadonR]

I would like to create a macro that can fill down cells A2 through C in Sheet3, based on how many rows there are in Sheet 1. For example, I have 20 rows on Sheet 1...

And the macro should copy down all cells in A2 through C and stop with the total of rows from Sheet1.

I created the basic macro below to illustrate what I want.

Sheets("Sheet3").Activate
Range("A2:C2").Select
Selection[B].AutoFill Destination:=Range("A2:C20")[/B], Type:=xlFillDefault

I would like the AutoFill Destination range to match the number of rows from Sheet1 which will dynamically change when fresh data is copied into that sheet.

 

[MARK858]

Maybe try....

    With Sheets("Sheet3").Range("A2:C2")
        .AutoFill Destination:=.Resize(Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
    End With

 

[JadonR]

Wow. That worked really well. What if I wanted to change it to the active sheet rather than "Sheet3"?

 

[MARK858]

Just remove the

Sheets("Sheet3").

(not that I'm a lover of using ActiveSheet in code)

 

[JadonR]

I'm currently getting a debug error and the macro is failing to fill down. The debug error is highlighting this code:

.AutoFill Destination:=.Resize(Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault

I'm not clear why this happens. Any ideas where I should start?

 

[MARK858]

Any ideas where I should start?

By telling us what the error message states

 

[JadonR]

It displays "Run-time error '1004':
Application-defined or object-defined error

 

[MARK858]

What does the message box say when you run the code below

Sub Testit()
  MsgBox Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

 

[JadonR]

What does the message box say when you run the code below

Sub Testit()
  MsgBox Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

It shows a "1".

 

[MARK858]

Then you can't resize by 0 (the row number from sheet1 minus 1 as your autofill data starts on row 2), and you also can't Autofill to the same row. Your Sheet1 only has a header or is blank in column A, your cells you are filling start on Row 2