Autofill column B based on dynamic range in column A in multiple sheets except some

[Commoner]

Hello,

I have battled with this for hours and tried this a multitude of ways based on results from threads in all of the Excel forums. I have a workbook with 36 sheets and the first column of each sheet, except the first two, will get updated about once a month so the range will change and all of the sheets have a different range in column A. I have a formula in B2 and want to be able to use VBA so that column B is autofilled to the range of column A. The following code works but the range is static and doesn't reference column A so a lot of times column B has way more cells filled than needed. 10,000 is currently the largest number of rows column A has in any of the sheets but that will change at some point. How can this be changed to autofill to the range of column A?


Sub AutoFill_Column_B()
Dim sh As Worksheet
For i = Sheets("Alpha").Index To Sheets("Beta").Index
Sheets(i).Select Replace:=False

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B10000")

Next i
End Sub


I have tried to incorporate the following but I can't figure out how to get it to work


Sub Macro1()
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").Copy Destination:=Range("B3:B" & Lr)

End Sub

[offthelip]

this shows you more or less how to do it, you need to sort out the sheet names


Sub test()

formu = Cells(2, 2).Formula






Worksheets("sheet2").Select
Lr = Range("A" & Rows.Count).End(xlUp).Row
Cells(2, 2).Formula = formu


For i = 3 To Lr
Range("B2").Copy Range(Cells(i, 2), Cells(i, 2))
Next i




End Sub

[Commoner]

this shows you more or less how to do it, you need to sort out the sheet names

Thank you for replying. I didn't know how to get that to work with all of the sheets except the first two. I had been searching for a solution for another problem and found a For Loop that goes through the sheets that I want the formulas to be placed in and autofilled. I also found that I can use other commands that I understood to enter a formula and for it to autofill the range.


Sub Insert_Formula()
Dim J As Integer
Dim LastRow As Long
On Error Resume Next

' work through sheets
For J = 3 To Sheets.Count ' from sheet 3 to last sheet
Sheets(J).Activate ' make the sheet active
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("B2:B" & LastRow).Formula = "Formula"

Next

End Sub