I would revise your table slightly, as follows:
TabName Column Width Format Font Bold
1.csv A 10 mm/dd/yyyy Times New Roman TRUE
1.csv B 10 ="00." Times New Roman TRUE
2.csv A 8 ="00." Arial FALSE
If you are willing to do that, then you can try the following code. It
loops through each row in the table, then loops across each column, finds
the value, then uses a Select Case statement to decide what to do. If you
encounter any errors, just set a breakpoint at the bottom of the routine on
(or right after) the line label "ErrUnknownError:". Then check the Locals
window to what the variables are.
If you decide to add any additional columns, just make sure that the
TabName comes first, then the Column. After that, you can probably put
things in any order. Be sure to add code to the Select Case statement to
handle the new instruction (column on the "Drivers" worksheet).
Option Explicit
Const strMsgBoxTitle = "Format Data Worksheets"
'----------------------------------------------------------------------
Public Sub FormatDataWorksheets()
'Variables that all refer to the "Drivers" worksheet.
Dim wbData As Workbook 'Workbook with Formatting & Data.
Dim wsDriver As Worksheet 'Worksheet with formatting instructions.
Dim rngAttributes As Range 'Top row of Driver worksheet.
Dim rngAttribute As Range 'A single attribute on the top row.
Dim lngNumInstructions As Long 'Total rows of formatting instructions.
Dim lngInstruction As Long 'Single row of formatting instructions.
Dim varCell As Variant 'Single cell on the "Drivers" worksheet.
'Variables that refer to the data worksheet being formatted.
Dim wsData As Worksheet
Dim rngColumn As Range
Dim strErrorMsg As String
On Error GoTo ErrUnknownError
Application.ScreenUpdating = False
Set wbData = ActiveWorkbook
Set wsDriver = wbData.Worksheets("Driver")
With wsDriver.UsedRange
Set rngAttributes = .Resize(1) 'Top row of labels.
lngNumInstructions = .Rows.Count - 1 'Top row of labels doesn't count.
End With
For lngInstruction = 1 To lngNumInstructions
Set wsData = Nothing
Set rngColumn = Nothing
For Each rngAttribute In rngAttributes
varCell = wsDriver.Cells(lngInstruction + 1, rngAttribute.Column)
Select Case rngAttribute
Case "TabName"
Set wsData = wbData.Worksheets(varCell)
Case "Column"
Set rngColumn = wsData.Columns(varCell)
Case "Width"
rngColumn.ColumnWidth = varCell
Case "Format"
rngColumn.NumberFormat = varCell
Case "Font"
rngColumn.Font.Name = varCell
Case "Bold"
rngColumn.Font.Bold = varCell
Case Else
GoTo ErrUnkownAttribute
End Select
Next rngAttribute
Next lngInstruction
GoTo ExitSub
ErrUnkownAttribute:
strErrorMsg = "Unkown format attribute."
MsgBox strErrorMsg, vbCritical + vbOKOnly, strMsgBoxTitle
GoTo ExitSub
ErrUnknownError:
strErrorMsg = "Unknown error."
MsgBox strErrorMsg, vbCritical + vbOKOnly, strMsgBoxTitle
GoTo ExitSub
ExitSub:
End Sub