Hello. Thank you in advance for any help or ideas you have! I have looked at several forums, but have not quite found a solution that works for me.
I am looking to create a master template that can be tailored through VBA to fit the requirements of a particular vendor. Depending on which vendor is selected the code would hide tabs, hide rows on certain tabs, change theme colors, and add logos to headers or footers.
The control table outlines all the changes that need to be made to the template based on vendor requirements. My current code finds the selected vendor in the control table and then runs through each row performing the desired action to the template. My code currently is only hiding tabs and tab rows. It is working as needed, but I am concerned about efficiency. There is a potential to have upward of 25 columns and 1,000’s of rows of data. I know referencing the sheet each time will definitely slow down the operation.
I believe an array is probably the correct way to process this, but I am having trouble grasping some of the concepts. I know how to load the table into an array, but I am struggling to figure out how to perform the lookup/ offsets that I am doing with just the table as it is currently. Any help would be appreciated.
Also, in column B of the control sheet I am using a match/indirect lookup to get the row number of each detail to be hidden/unhidden on each sheet. I like being able to see it all for troubleshooting, but it’s another thing to have to maintain when updates are made. Is there a way to quickly do the lookups in VBA and store the results to be used in conjunction with the hide rows procedure? The lookups need to allow for the flexibility that the rows of each sheet could be edited/deleted/reordered etc. in the future. Any thoughts of a better way?
All Data in the example is arbitrary and for illustration purposes, I just used car manufacturers as an example.
Cross posted at:
ExcelForum.com
I am looking to create a master template that can be tailored through VBA to fit the requirements of a particular vendor. Depending on which vendor is selected the code would hide tabs, hide rows on certain tabs, change theme colors, and add logos to headers or footers.
The control table outlines all the changes that need to be made to the template based on vendor requirements. My current code finds the selected vendor in the control table and then runs through each row performing the desired action to the template. My code currently is only hiding tabs and tab rows. It is working as needed, but I am concerned about efficiency. There is a potential to have upward of 25 columns and 1,000’s of rows of data. I know referencing the sheet each time will definitely slow down the operation.
I believe an array is probably the correct way to process this, but I am having trouble grasping some of the concepts. I know how to load the table into an array, but I am struggling to figure out how to perform the lookup/ offsets that I am doing with just the table as it is currently. Any help would be appreciated.
Also, in column B of the control sheet I am using a match/indirect lookup to get the row number of each detail to be hidden/unhidden on each sheet. I like being able to see it all for troubleshooting, but it’s another thing to have to maintain when updates are made. Is there a way to quickly do the lookups in VBA and store the results to be used in conjunction with the hide rows procedure? The lookups need to allow for the flexibility that the rows of each sheet could be edited/deleted/reordered etc. in the future. Any thoughts of a better way?
All Data in the example is arbitrary and for illustration purposes, I just used car manufacturers as an example.
Cross posted at:
ExcelForum.com
Last edited: