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:
Sheet to be manipulated: Please note the starting row with "Model" can vary from sheet to sheet
My Current Code:
Cross posted:
Chandoo.org
ExcelForum.com
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:
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