VBA Customizable Excel Master Template w/ Array

jdutle

New Member
Joined
Dec 8, 2015
Messages
17
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
ABCDEFGHI
1
2Tab NameRow LookupSheet DetailsHondaLexusMercedes
3Theme Colors
4N/AN/AHeader Fill
5N/AN/AFontSelect Manufacturer:
6N/AN/AOuter BorderMercedes
7N/AN/AInside Border
8SedanHide Tab
9SedanLogo PositionRightHeaderRightFooter
10Sedan4Model
11Sedan6Pricex
12Sedan7Chassis
13Sedan8Brakesx
14SUVHide Tab
15SUVLogo PositionRightHeader
16SUV5Modelx
17SUV7Heightx
18SUV8Widthx
19SUV9Turn Radiusx
20SUV10Towing Capacityxx
21SUV11Warranty
22SUV12Colors
23TruckHide Tab
24TruckLogo PositionRightFooter
25Truck6Modelx
26Truck8Pricex
27Truck9Enclosure
28Truck10Towing Capacityx
29Truck11Horse Powerx
30
31
32
33
Control
Cell Formulas
RangeFormula
A9:A13A9=$A$8
B10:B13,B25:B29,B16:B22B10=MATCH(C10,INDIRECT("'"&$A10&"'!A1:A60"),0)
A15:A22A15=$A$14
A24:A29A24=$A$23


Sheet to be manipulated:
Please note the starting row with "Model" can vary from sheet to sheet
Example.xlsm
ABCDEFGHIJ
1Sedan
2
3
4ModelModel 1Model 2Model 3Model 4
5
6Price
7Chassis
8Brakes
Sedan



My Current Code:
VBA 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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,948
Messages
6,175,579
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top