add extra step to macro

[Pete]

See attached workbook.......
Hi

Need to add a extra step to the current macro button in worksheet "Volume Summary", when the user adds or clicks the macro button "Refresh & Update Data Fields".

I want the macro to add defined name ranges from column F:EC for all the tables in the worksheet...and also to update when a new client name is added to the worksheet "Deal Selection" and then the user returns back to the workhseet "Volume Summary" at click the "Refresh & Update Data Fields" - macro button......

so altogether there are six tables in the volume summary worksheet....

i believe that a similiar function to hightlighing columns E:BC and then the user selects CTRL + SHIFT + F3 which allows you to set name ranges should do the trick.........

[mdmackillop]

Are you wanting 6 named ranges, one for each table or 128 named ranges for each table. In either case, what is the logic for naming the ranges.

[Pete]

Hi

Are you wanting 6 named ranges, one for each table - yes..... it is important to remember that as the user adds new client names to the worksheet "Deal Selection" column B and then click the macro but in worksheet "Volume Summary" the table adjust accordingly and like wise if a name is remove from column B worksheet "Deal Selection"..

do not ask me the logic behind this.....i have a complete idiot as a end user, who wants it......

[mdmackillop]

Add the following function in a standard module

Function NameRows(Cel As Range)
NameRows = Range(Cel, Cel.End(xlDown)).Rows.Count
End Function



Use the following formula to create a dynamic range for each table (adjusted as required)

=OFFSET('Volume Summary'!$F$8,0,0,NameRows('Volume Summary'!$B$8),128)

[Pete]

test it out and get back to you........

thanks for the feedback....