need code to loop ranges as source for charts

[Louie]

Just starting with VBA...

I have a reporting package that includes summary charts for 19 regions, each
region's data is set up in a named range. Each named range includes 3
columns (service, amt, & %) and I update this every 28 days by resorting the
range using 'amount' as the sort key. Each region includes 47 rows but only
the last 25 rows are the named range used for the chart data.

My current VBA recorded 19 seperate sorts, can anyone tell me how to sort 1x
for the 19 named ranges?

Louie appreciates your help!

 

[Bernie Deitrick]

Louie,

How to do it depends on your naming convention. Or, don't use VBA. Set up a table of formulas to
extract the data required, and use that table as the source of your graph.

HTH,
Bernie
MS Excel MVP

 

[Louie]

Bernie - I have a table already set up on another worksheet that extracts the
data from BW. My charts page pulls this data into named ranges but this data
has to be re-sorted. I began sorting manually 19x, then recorded a macro to
sort each named range 19x, my question is can I list 19 range names once and
sort?

Here's 1 of 19, 'dgdata' is one named range, can I list the other 18 and
then sort?

Application.Goto Reference:="dgdata"
Selection.Sort Key1:=Range("c113"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[Bernie Deitrick]

Louie,

Sub LouiesSort()
Dim myNames As Variant
Dim myN As Variant

'Change this to include all 19 names
myNames = Array("dgdata", "otherdata1", "otherdata2", "otherdata3", "otherdata4")

'Change xlYes to xlNo if you don't have headers included in your named ranges
For Each myN In myNames
Range(myN).Sort Key1:=Range(myN).Cells(2, 1), Order1:=xlDescending, Header:=xlYes _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next myN

End Sub


HTH,
Bernie
MS Excel MVP