How can I edit this recorded macro to include all sheets?

[strat919]

I created this macro by recording. I selected all sheets and the code shows 13 sheets. So I can only use this macro with 13 sheets. I would like to be able to use it for any amount of sheets.

All help is much appreciated

Sub FindDistance()
'
' FindDistance Macro
'

'
    Range("E1").Select
    Sheets(Array("Sheet13", "Sheet12", "Sheet11", "Sheet10", "Sheet9", "Sheet8", "Sheet7" _
        , "Sheet6", "Sheet5", "Sheet4", "Sheet3", "Sheet2", "Sheet1")).Select
    Sheets("Sheet13").Activate
    Range("E1").Select
    ActiveCell.FormulaR1C1 = _
        "=6371*ACOS(COS(RADIANS(90-RC[-4]))*COS(RADIANS(90-RC[-2]))+SIN(RADIANS(90-RC[-4]))*SIN(RADIANS(90-RC[-2]))*COS(RADIANS(RC[-3]-RC[-1])))/1.609"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E60195")
    Range("E1:E60195").Select
End Sub

 

[Michael M]

A bit shorter code, but do you want ALL sheets to have this applied to ?

Sub FindDistance()
    Sheets(Array("Sheet13", "Sheet12", "Sheet11", "Sheet10", "Sheet9", "Sheet8", "Sheet7", "Sheet6", "Sheet5", "Sheet4", "Sheet3", "Sheet2", "Sheet1")).Select
    Sheets("Sheet13").Range("E1:E60195").Formula = "=6371*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-C1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-C1))*COS(RADIANS(B1-D1)))/1.609"
End Sub

 

[strat919]

Yes..... all sheets. Sometimes there may be 5 sheets or any amount of sheets. Also there may be variable amounts of rows in each sheet.

 

[My Aswer Is This]

To do all sheets try this:

Sub All_Sheets()
'Modified 7/18/2019 11:46:30 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Sheets.Count
    Sheets(i).Range("E1:E60195").Formula = "=6371*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-C1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-C1))*COS(RADIANS(B1-D1)))/1.609"
Next
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Ok try this

Sub FindDistance()
Dim ws As Worksheet, lr As Long
For Each ws In Worksheets
lr = ws.Cells(Rows.Count, "E").End(xlUp).Row
    ws.Range("E1:E" & lr).Formula = "=6371*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-C1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-C1))*COS(RADIANS(B1-D1)))/1.609"
Next ws
End Sub

 

[My Aswer Is This]

Now that your saying the number of rows will not always be the same.
Try this:

Sub All_Sheets()
'Modified  7/18/2019  11:57:43 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
For i = 1 To Sheets.Count
    Lastrow = Sheets(i).Cells(Rows.Count, "E").End(xlUp).Row
        Sheets(i).Range("E1:E" & Lastrow).Formula = "=6371*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-C1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-C1))*COS(RADIANS(B1-D1)))/1.609"
    Next
Application.ScreenUpdating = True
End Sub

 

[strat919]

It worked perfectly for the 13 sheets. I'm having issues with the workbook with all my formulas. It seems to be remembering data after I delete all rows and columns. Is there a way to "flush" the workbook so it's as if there were no data in it? Reset it, but keep all formulas. I've never had this problem before.

 

[Michael M]

you mean you want column "E" of every sheet cleared ?
OR
Every cell on each sheet cleared ??
But if you clear every cell...what would be the point of the formula in Col "E" on every sheet ?

 

[Michael M]

Maybe this is what you want...

Sub FindDistance()
Dim ws As Worksheet, lr As Long
For Each ws In Worksheets
lr = ws.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    ws.Range("E1:E" & lr).Formula = "=6371*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-C1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-C1))*COS(RADIANS(B1-D1)))/1.609"
Next ws
End Sub