palaeontology
Active Member
- Joined
- May 12, 2017
- Messages
- 444
- Office Version
- 2016
- Platform
- Windows
Hi all,
I know how to make dynamic named ranges using the ws ribbon functions 'Formulas' and 'Define Name', however I have a large number of spreadsheet, and each spreadsheet will need a large number of dynamic named ranges.
My VBA coding is poor, at best, so I'm wondering if it's possible to have code create these numerous dynamic named ranges automatically.
The coding would only need to look at the ws called .... Entry
The 'Entry' ws might look something like this ...
In the image above, there is a maths class that goes by the code .... 10MAA501 ... there are other classes in this Entry ws, for example 10MAA502, 10MAA503, 10MAA601, 10MAA602, 10MAA701, 10MAA702 etc etc etc
From time to time, new students will be added to some of the classes, so I need some code to create a dynamic named range for each class in the list.
So for example, the 10MAA501 range of data currently spans the range .... Entry!A8:AH34 .... and the 10MAA502 range of data currently spans the range .... Entry!A35:AH60 but when new students are added, the ws is re-sorted by column C, so the range for a class would change to something else, for example, 10MAA501 might change to Entry!A8:AH37, and 10MAA502 might change to Entry!A38:AH67.
Is VBA able to create dynamic named ranges or will I need to do each one by hand .... a task I'm not looking forward to and would need to redo for each of 23 workbooks every 6 months.
Kind regards,
Chris
I know how to make dynamic named ranges using the ws ribbon functions 'Formulas' and 'Define Name', however I have a large number of spreadsheet, and each spreadsheet will need a large number of dynamic named ranges.
My VBA coding is poor, at best, so I'm wondering if it's possible to have code create these numerous dynamic named ranges automatically.
The coding would only need to look at the ws called .... Entry
The 'Entry' ws might look something like this ...
In the image above, there is a maths class that goes by the code .... 10MAA501 ... there are other classes in this Entry ws, for example 10MAA502, 10MAA503, 10MAA601, 10MAA602, 10MAA701, 10MAA702 etc etc etc
From time to time, new students will be added to some of the classes, so I need some code to create a dynamic named range for each class in the list.
So for example, the 10MAA501 range of data currently spans the range .... Entry!A8:AH34 .... and the 10MAA502 range of data currently spans the range .... Entry!A35:AH60 but when new students are added, the ws is re-sorted by column C, so the range for a class would change to something else, for example, 10MAA501 might change to Entry!A8:AH37, and 10MAA502 might change to Entry!A38:AH67.
Is VBA able to create dynamic named ranges or will I need to do each one by hand .... a task I'm not looking forward to and would need to redo for each of 23 workbooks every 6 months.
Kind regards,
Chris