Changing Worksheets' Code Name
Why dim srcSheet as myWorksheet when you can just change the Worksheet's Code Names?
Enumerate the Columns and Header Rows
For each worksheet, I generate an Enum to enumerate the [First Column], [Last Column], and [Header Row] columns. Enum members can be made hidden by enclosing them in brackets and giving them names with special characters. Here is some sample code. Update the enum, and the code won't fail if I later decide to rearrange the columns, add columns, or move the data.
Right-clicking the Object Browser gives you the option to show hidden members of Object and Enums.
Enums are available to Intellisense
Public Enum EnumWSDataColumns
dcOrderDate = 3
[_dcFirstColumn] = dcOrderDate
[_dcLastColumn] = dcTotal
dcHeaderRow = 4
Dim r As Long, c As Long
Dim value As String * 10
For r = dcHeaderRow To .Cells(.Rows.count, [_dcFirstColumn]).End(xlUp).Row
For c = [_dcFirstColumn] To [_dcLastColumn]
value = .Cells(r, c)
Debug.Print value; "|";
Dim n As Long
n = ([_dcLastColumn] - [_dcFirstColumn] + 1) * (Len(value) + 1)
Debug.Print String(n, "-")