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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Michael M

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

ADVERTISEMENT

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

ADVERTISEMENT

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
19,090
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
19,090
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,175
Messages
5,509,612
Members
408,743
Latest member
leen1234

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top