One question about using Macro

Question

I record macro, the code as below displayed. The purpose of this Macro is:

Step 1: click commandbar "Refresh All" as below red marked.

Step 2: Enter the word "Description" in Range B1. And use Data-Text to Columns to change date format.

Now the problem is: When run the macro, it fail to run the step 1, and directly skip to run step 2. Could you please give instruction how to revise the code to make step 1 done ? Thanks!

Here is the code for this macro:

Sub DescriptionDate()

'

' DescriptionDate Macro

' Refresh data, add Description, change date format, refresh Pivot, Save

'

' Keyboard Shortcut: Ctrl+z

'

    Selection.UnMerge

    ActiveWorkbook.Names.Add Name:="SAPCrosstab1", RefersToR1C1:= _

        "=BOBJ!R1C1:R5102C31"

    Selection.UnMerge

    ActiveSheet.Range("B1") = "Description"

    Columns("D:D").Select

    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("E:E").Select

    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("F:F").Select

    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("G:G").Select

    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("H:H").Select

    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("I:I").Select

    Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("J:J").Select

    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("K:K").Select

    Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("L:L").Select

    Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    Columns("M:M").Select

    Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 3), TrailingMinusNumbers:=True

    ActiveWorkbook.Save

    End Sub

Answer 1

The add-in I installed is called "Analysis" for Excel.

Answer 2

How did you get 'Anlaysis' tab on your ribbon? Which add-in are you using?

If I can get it to show up on my ribbon and get your file then I can try to find the code behind the 'Refresh All' button.

Answer 3

Yeah, I understand what you said, agree with you that the code from macro is incorrect.

And I also have no idea how to find the equivalent VBA code to make step1 happen.

Already tried your suggestion "Try to record a new macro and do only the first step. See what you get", but cannot be workable. 🤣😂😭

Answer 4

Not all actions from the user interface get recorded in a macro. You will have to find the equivalent VBA code for the action you want and add to the macro. I do not have the 'Analysis' tab on my setup of Excel so unable to help you there.

Something like

    ActiveWorkbook.RefreshAll

Try to record a new macro and do only the first step. See what you get... it may be something similar to

    Selection.UnMerge

    ActiveWorkbook.Names.Add Name:="SAPCrosstab1", RefersToR1C1:= _

        "=BOBJ!R1C1:R5102C31"

    Selection.UnMerge