VBA Customizable Excel Master Template w/ Array

[jdutle]

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?

Thank you for any help!

All Data in the example is arbitrary and for illustration purposes, I just used car manufacturers as an example.


Control Sheet:

Example.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Tab Name	Row Lookup	Sheet Details	Honda	Lexus	Mercedes
3	Theme Colors
4	N/A	N/A	Header Fill
5	N/A	N/A	Font					Select Manufacturer:
6	N/A	N/A	Outer Border					Mercedes
7	N/A	N/A	Inside Border
8	Sedan			Hide Tab
9	Sedan		Logo Position		RightHeader	RightFooter
10	Sedan	4	Model
11	Sedan	6	Price		x
12	Sedan	7	Chassis
13	Sedan	8	Brakes			x
14	SUV					Hide Tab
15	SUV		Logo Position		RightHeader
16	SUV	5	Model		x
17	SUV	7	Height	x
18	SUV	8	Width	x
19	SUV	9	Turn Radius		x
20	SUV	10	Towing Capacity	x	x
21	SUV	11	Warranty
22	SUV	12	Colors
23	Truck				Hide Tab
24	Truck		Logo Position			RightFooter
25	Truck	6	Model	x
26	Truck	8	Price			x
27	Truck	9	Enclosure
28	Truck	10	Towing Capacity			x
29	Truck	11	Horse Power	x
30
31
32
33
Control

Cell Formulas
Range	Formula
A9:A13	A9	=$A$8
B10:B13,B25:B29,B16:B22	B10	=MATCH(C10,INDIRECT("'"&$A10&"'!A1:A60"),0)
A15:A22	A15	=$A$14
A24:A29	A24	=$A$23

Sheet to be manipulated: Please note the starting row with "Model" can vary from sheet to sheet

Example.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Sedan
2
3
4	Model		Model 1			Model 2		Model 3		Model 4
5
6	Price
7	Chassis
8	Brakes
Sedan

My Current Code:

Sub Reset()
Dim ws As Worksheet

    For Each ws In Sheets: ws.Visible = True: Next
    For Each ws In Sheets: ws.Rows.EntireRow.Hidden = False: Next
End Sub
Sub HideSheetRows()

    Dim tbName As String
    Dim hrow As String
    Dim manuf As String
    Dim Tlookup As Range
    Dim r As Range
    
    'Attempt to speed up code
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    manuf = Sheets("Control").Range("ManufSel")
    
    'Lookup Manufacturer selected from drop down to get column
    Set Tlookup = Sheets("Control").Cells(2, 1).EntireRow.Find(what:=manuf, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    
    'Resize lookup range to fit entire data set
    For Each r In Sheets("Control").Range(Tlookup.Address).Resize(1000, 1).Cells
      
        tbName = Cells(r.Row, 1) ' get tab name from column A
        hrow = Cells(r.Row, 2) ' get tab row number, from lookup column
            
        If r.Value = "x" Then ' Hide tab rows marked with an X
            On Error Resume Next
            Sheets(tbName).Rows(hrow).EntireRow.Hidden = True
                
            ElseIf r.Value = "Hide Tab" Then ' Hide tabs marked with Hide Tab
                Sheets(tbName).Visible = False
                
            ElseIf r.Value = "RightHeader" Then ' Add logo to right header of sheet
                ' Run macro to add Header
                
            ElseIf r.Value = "RightFooter" Then ' Add logo to left footer of sheet
                ' Run macro to add Footer
        End If
    
    Next r
    
    'Attempt to speed up code - RESET
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
End Sub

Cross posted:
Chandoo.org
ExcelForum.com