Dynamic Name Range With Data Analysis, Macro

[Jarke]

Hi,

My goal is to create a macro where it updates my data analysis, such as descriptive analysis, histogram, correlation etc. I've managed to record a macro to do this.

The problem is that i will update my data, add more rows to it, thus the ranges withing the data analysis (histogram..) will change automatically with my extra input. The best solution would be to put dynamic named ranges in the data analysis ranges. This does unfortunately not work for me yet.

It works to put a dynamic name range when creating the macro, but when I want to use the macro it says: "Input range missing. Please Enter." Thus my dynamic range disappears from the macro.

How should I go about this? Am I missing something?

How can I use dynamic name ranges in a macro?

I managed to get abit furher, but the dynamic name range was replaced with $M$17:$M$29

My name range name is "Test"

Could I replace the range in the code with: =Sheet1!Test

Would love any help!

- I want my macro to update my data analysis with just a click, without having to update the ranges within the analysis tools, thus using dynamic name ranges.

VBA code:
Sub Macro10()
'
' Macro10 Macro
'

'
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$M$17:$M$29"), _
ActiveSheet.Range("$P$19"), "C", False, True
Range("D45").Select
End Sub
Sub Macro11()
'
' Macro11 Macro
'

'
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$M$17:$M$29"), _
ActiveSheet.Range("$P$19"), "C", False, True
Range("E37").Select
End Sub

 

[Jarke]

ah yes exactly, but that is okey for me atm, probably easier to just learn one way first.

The second range in the Histogram can stay the same, it's a referance data, so don't need to change that.

Basically I now have two working codes that I want to put together. The seperate codes are:

Sub UpdateHistogram()
Dim MyRange As Range

    With Sheets("Sheet1")
    Set MyRange = .Range("L17:L" & .Cells(.Rows.Count, "L").End(xlUp).Row)

        Application.Run "ATPVBAEN.XLAM!Histogram", MyRange _
        , .Range("$U$19"), .Range("$M$17:$M$25"), False, _
        False, False, False
    End With
End Sub

And

Sub UpdateDS()
    Dim MyRange As Range
    
    With Sheets("Sheet1")
        Set MyRange = .Range("M17:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
        Application.Run "ATPVBAEN.XLAM!Descr", MyRange, .Range("$P$19"), "C", False, True
        .Range("D45").Select
    End With
End Sub

My previous post was an attempt to put them together. Do you know how to put them together?

 

[Marcelo Branco]

Try

Sub UpdateBoth()
    Dim Range1 As Range, Range2 As Range
    
    With Sheets("Sheet1")
        Set Range1 = .Range("L17:L" & .Cells(.Rows.Count, "L").End(xlUp).Row)
        Set Range2 = .Range("M17:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
        
        Application.Run "ATPVBAEN.XLAM!Histogram", Range1, _
                .Range("$U$19"), .Range("$M$17:$M$25"), False, False, False, False
        
        Application.Run "ATPVBAEN.XLAM!Descr", Range2, .Range("$P$19"), "C", False, True
    End With
End Sub

M.

 

[Jarke]

Super! Works like a charm mate. Thanks alot!

Will continue with the macro tomorrow or another day probably, I hope I can ask you again here if I stumble upon a problem. Cheers for today!

 

[Marcelo Branco]

You are welcome. Glad to help

M.