Oke, so.... if I understand it correctly then the only thing I had to change is the Sheet2 column were the TextCode is registered. This is at this moment column H. Just as you remarked clear in the code.
In Sheet1 its no issue that the TextCodes are registered in column AA and so further on. Oke.

Then it should work, but.. the code still gives an error on below last rule;

For i = LBound(varNames, 2) To UBound(varNames, 2)
z = firstSh1 - 1 + i
If Application.CountA(.Range(.Cells(6, z), .Cells(LRowSh1, z))) = 0 Then GoTo Skip
For j = LBound(varCodes) To UBound(varCodes)
x = j + 1
codeSum = 0
For Each rngC In .Range(.Cells(6, z), .Cells(LRowSh1, z)).SpecialCells(xlCellTypeConstants)
If InStr(rngC, Chr(10)) = 0 Then
sCode = Left(Split(rngC, "[")(0), Len(Split(rngC, "[")(0)) - 1)
iNmbr = Left(Split(rngC, "[")(1), Len(Split(rngC, "[")(1)) - 1)

The last rule gives the error (iNmbr....).

Whats the problem here ?

regards, Johan