How can I edit this recorded macro to include all sheets?
Results 1 to 9 of 9

Thread: How can I edit this recorded macro to include all sheets?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,541
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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 by Michael M; Jul 18th, 2019 at 11:43 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by strat919; Jul 18th, 2019 at 11:48 PM.

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,541
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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 by Michael M; Jul 18th, 2019 at 11:55 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,541
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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 ?
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,541
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •