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.

 

[JadonR]

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

That's odd, because Sheet1 contains 6156 rows of records. Why wouldn't it be seeing that? Is it because I have Column A hidden in Sheet1?

 

[MARK858]

Is it because I have Column A hidden in Sheet1?

Works fine for me with hidden column A (I just filled to row 25 in column A, hid the column and the message box returned 25), if you have filters applied to column A, it will only see the visible data (same with hidden rows).

 

[MARK858]

Do you still return a 1 with

Sub testme()
MsgBox Sheets("Sheet1").Columns(1).Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
End Sub

 

[JadonR]

Do you still return a 1 with

Sub testme()
MsgBox Sheets("Sheet1").Columns(1).Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
End Sub

I confirmed there are no filters set for Sheet1. Running your second macro also returns a message of "1".

 

[MARK858]

Then I am afraid I don't know without seeing the workbook