VBA Code for Printing a various Named ranges but sort those Named Ranges by order from a league table

[reacher14]

Hi
First off I am no expert on VBA. also I am not sure if I am explaining this correctly.
I have this code to print Named ranges
Sub Print_Info_v1()

Worksheets("Funds").Activate
'PFS that are positive
'ActiveSheet.Range("PFS162_,PFS166_,PFS114_,PFS119_,PFS19_,PFS66_,PFS118_,PFS140_,PFS107_,PFS51_,PFS167_,PFS165_,PFS13_,").PrintOut

ActiveSheet.Range("PFS162_,PFS166_,PFS114_,PFS119_,PFS19_,PFS66_,PFS118_,PFS140_,PFS107_,PFS51_,PFS167_,PFS165_,PFS13_,Data").PrintOut

End Sub

That code works fine, However all these PFS*** are Financial Portfolios that I have been monitoring and that swap positions what I want to do is print Portfolio that are represent by their position in league table of PFS***
This League table I have so is it possible to print the PFS in their league order ? ie so page 1 is position 1 and if so how would I tackle it - thanks

 

[John_w]

Assuming the League table is a proper Excel table on a sheet named "Table" and the names of the named ranges are in column 3 of that table, try this macro:

Public Sub Print_Named_Ranges_In_Table()
   
    Dim table As ListObject
    Dim r As Long, printRanges As String
   
    Set table = Worksheets("Table").ListObjects(1)

    printRanges = ""
    With table
        For r = 1 To .DataBodyRange.Rows.Count
            printRanges = printRanges & .DataBodyRange(r, 3).Value & "_,"
        Next
    End With
    If printRanges <> "" Then Worksheets("Funds").Range(Left(printRanges, Len(printRanges) - 1)).PrintOut

End Sub

 

[reacher14]

Thanks, John It is not a proper table it is a self-sorting using this formula =INDEX($B$170:$EB$170,MATCH(LARGE($B$174:$EB$174, M183),$B$174:$EB$174,0))but I will take a look and see if I can make a separate table - thanks