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

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:)

Code:
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
A bit shorter code, but do you want ALL sheets to have this applied to ?

Code:
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
 
Last edited:

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
To do all sheets try this:
Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
Ok try this

Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Now that your saying the number of rows will not always be the same.
Try this:
Code:
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

Board Regular
Joined
May 15, 2019
Messages
54
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
Maybe this is what you want...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,326
Messages
5,486,172
Members
407,536
Latest member
farrukhikram

This Week's Hot Topics

Top