Skip to Content

VBA Refresh Macro W/ Custom Excel Functions

Hi Experts,

I am trying to create a refresh macro which performs the following steps:

1. Remove Excel Subtotals

2. Refresh Work Sheet

3. Add subtotal

I was able to write the refresh piece but am having difficulty with the syntax for steps #1 and 3 - I have very little experience with VBA. Any help would be much appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • May 18, 2017 at 06:06 AM

    Why not to Google "vba add subtotals"

    A lot of samples!

    Range.Subtotal Method (Excel)

    https://msdn.microsoft.com/ru-ru/library/office/ff838166.aspx

    To remove:

    RemoveSubtotal

    But why not to use EPM report with required subtotals?

    Add comment
    10|10000 characters needed characters exceeded

    • Sorry, but the code mentioned in the above post is incorrect in general.

      If you want some help - please show report screenshot (with all dimensions shown) and explain the requirements in details:

      What do you want to sort

      What subtotals you want to add.

  • May 18, 2017 at 01:47 PM

    Hi Kevin,

    Definitely you need to get some VBA training... Try to create yourself some procedures that will do something with Excel sheet (even without EPM API involved).

    "I can get that just from recording those simple steps in excel," - bad idea to use generated (recorded) code, try to write it yourself!

    And for sure you can add code before and after "EPMexample.RefreshActiveSheet"

    For example:

    Sub Button1_Click()
    MsgBox "Before Refresh" ' Any code here
    EPMexample.RefreshActiveSheet
    MsgBox "After Refresh" ' Any code here
    End Sub
    Add comment
    10|10000 characters needed characters exceeded

  • May 18, 2017 at 02:12 PM

    Easy!

    Selection is the range object containing the selected cells.

    And at the beginning you are assuming that some range is selected by user... strange...

    Then after refresh, the initial selection is replaced by single cell selection - you get the error!

    After refresh you can get the range you want to sort and add subtotals using:

    GetDataTopLeftCell

    GetDataBottomRightCell

    Please read API help...

    Add comment
    10|10000 characters needed characters exceeded