none
Help making a recorded macro more efficient RRS feed

  • Question

  • I have recorded a few macros and combined them to get my workbook to do what I want it to.  But I know that recorded macros are not the most efficient and I imagine there is a better way to do what I am doing.  I have a main register sheet, much like a bank account register, with 18 essentially identical sheets for each “account” in the system.  These sheets are each a “copy” of the “register” sheet by using a formula similar to “=(Register!D256)” in all of the various cells.

     

    1) The first thing I want done is for the date field to be filtered/sorted oldest to newest. The code that the system recorded is as follows:

     

        ActiveWorkbook.Worksheets("Register").AutoFilter.Sort.SortFields.Clear

        ActiveWorkbook.Worksheets("Register").AutoFilter.Sort.SortFields.Add Key:= _

            Range("A13:A318"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _

            :=xlSortNormal

        With ActiveWorkbook.Worksheets("Register").AutoFilter.Sort

            .Header = xlYes

            .MatchCase = False

            .Orientation = xlTopToBottom

            .SortMethod = xlPinYin

            .Apply

        End With

     

    2) The second thing I want done is for each sheet to sort out only the records that contain data for that particular account.

     

    In the seventh field/column of each sheet I have the formula “=IF($B53=G$4,G$4,IF($C53=G$4,G$4,"  "))”  This gives me the account name that is typed into cell G4 on each line/record where that account if referenced.  I have a filter set and the following recorded “code” opens all records and then filters out only the records that contain the account data:   

     

    ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

    ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

     

     

    3) The third thing I want done is for a macro to go from one sheet to the next 18 sheets and run the process from step 2 above.  To do this I used the following “code.”

        ActiveSheet.Next.Select

     

    4) Finally the fourth thing I want done is for the “SUMMARY” sheet to be left active at the end.

     

    --This all leave me with a cumbersome macro as follows:

     

     

        ActiveWorkbook.Worksheets("Register").AutoFilter.Sort.SortFields.Clear

        ActiveWorkbook.Worksheets("Register").AutoFilter.Sort.SortFields.Add Key:= _

            Range("A13:A318"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _

            :=xlSortNormal

        With ActiveWorkbook.Worksheets("Register").AutoFilter.Sort

            .Header = xlYes

            .MatchCase = False

            .Orientation = xlTopToBottom

            .SortMethod = xlPinYin

            .Apply

        End With

        Sheets("Statements").Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

            ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7

        ActiveSheet.Range("$A$4:$H$497").AutoFilter Field:=7, Criteria1:="<>"

        ActiveSheet.Next.Select

        Sheets("SUMMARY").Select

    End Sub

     

    I have worked with this kind of thing years ago, but I don’t remember how it was that I converted those processes to make them more efficient.

     

    If someone would be willing to help this novice out with figuring out how to make it run smoother I would be very greatful!  Or as an alternate, perhaps someone would recommend a good “For Dummies” resource I could use to figure it out myself.

     

    Thank you!

    Thursday, December 24, 2015 5:01 PM

Answers

  • Re:  more efficient code

    The first part of your code could be condensed/improved...

    With ActiveWorkbook.Worksheets("Register").AutoFilter.Sort
      .SortFields.Clear
      .SortFields.Add Key:=ActiveWorkbook.Worksheets("Register").Range("A13:A318"), _
                     SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = 1
      .Apply
    End With

    '---
    Note:  code inside a With | End With statement is added to the With statement by prefixing the code with a dot.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, December 25, 2015 5:17 PM

All replies

  • Hi Jeff,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel
    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, December 25, 2015 8:58 AM
  • Re:  more efficient code

    The first part of your code could be condensed/improved...

    With ActiveWorkbook.Worksheets("Register").AutoFilter.Sort
      .SortFields.Clear
      .SortFields.Add Key:=ActiveWorkbook.Worksheets("Register").Range("A13:A318"), _
                     SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = 1
      .Apply
    End With

    '---
    Note:  code inside a With | End With statement is added to the With statement by prefixing the code with a dot.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, December 25, 2015 5:17 PM