Can you chop this up into smaller functions, or subroutines, that produce an expected output? Then call each subroutine, so you can troubleshoot (and understand how to pass the variable into the routines that need them). BTW, I didn't notice any loop.
In the case of the sheet name, build another variable like:
thesheetname(0)= ActiveSheet.Name & "With" & Nsubperiods & "Periods"