We would need to change parts, or to make it a variable
for example, this line has a varible for the number of rows (namescount + 4), so we would need to work out where the new 'J' would be.
Code:
Set SSOldData = Worksheets("SS Hours Scoutbook Money").Range("a5:j" & NamesCount + 4)
If we can assume that the word 'Prizes will always be the last column in the table and it will always be in row 4 we can do
Code:
SSLastCol = Chr(64 + Worksheets("SS Hours Scoutbook Money").Range("4:4").Find(what:="Prizes").Column)
Then the orginal row becomes
Code:
Set SSOldData = Worksheets("SS Hours Scoutbook Money").Range("a5:" & SSLastCol & NamesCount + 4)
Might need updating in 7 or 8 places, easy enough
Edit
and the totals columns....