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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,974
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,974
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,974
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,974
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,679
Messages
5,488,226
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top