Commoner
11-22-2017, 07:56 AM
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
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