vba autofill / error range class

[Rotating_Potato]

Hello,

Desperately in the need of the advise of the forum's experts. I have a code:

Sub autofill()


Workbooks(file_main).Worksheets("sheet1").Activate
ActiveSheet.Range("R52").End(xlDown).Select
ActiveSheet.Range(Selection, Selection.End(xlToLeft)).Select
Selection.autofill Destination:=ActiveSheet.Range(Range("R52").End(xlUp).Offset(-1, 0), Range("HH52").End(xlDown)), Type:=xlFillDefault


End Sub

The above produces an error 1004 of range class failed. Tried multiple variations of the code, but in the essence followed same logic.

The goal that I want to achieve: find the last cell in column "R", then find the last cell in that row. Finally, select the range and autofill up to the row below the last row. Hopefully this explains what I want... Struggling with it already for hours...Please help.

 

[Rotating_Potato]

Mike, nice try, but you semi-coded the column in this line ActiveSheet.Range(Cells(Lr, 2), Cells(Lr, LC)).AutoFill Destination:=Selection.
It does the job, but if somebody enter a new column the macro won't work. Correct me if I'm wrong.
I do appreciate a lot your help & effort!

 

[ukmikeb]

Hi

Sorry, I am getting confused about the cells you want to autofill.

What is your starting point? Is it the Active cell as you open the sheet?

Then having found the last occupied row, do you want to find the leftmost cell or the rightmost cell in that row?

Do you want to autofill up to row 2 or up to the last row before a blank row?

 

[MARK858]

alright, here's what I got:

a) The line highlighted is x.AutoFill Range(x, Range(Cells(2, "R"), Cells(2, LstCo))). It produces Range of Object Global failed" error.
b) I don't know how to answer this question. Can you explain what u mean?
c) The code provided by you unfortunately doesn't even select the range...

With b) I wanted to know what was in the cells.... numbers, letters, formulas etc.
with c) I should hope it doesn't select anything. You (in most cases) don't need to select a range to work with it (in fact you shouldn't).

Now you need to answer Mike's questions in post #12