Sub PopulateExcel is going to get really big if you have all that code in every Case statement, plus it will be difficult to troubleshoot, maintain, and modify your code.
Instead, write a different sub to handle each Role and call those subs in the Select Case
Case "CRM": MakeCRM
Case Other: MakeOther
Case YetOther: MakeYetOther
Case Etc: Make Etc
'
'
End Sub
Sub MakeCRM()
CRM Code here
End Sub
Small Subs and Functions used by all Role Making Subs here
Avoid the use of Sheets(numerical Index), Instead use the Tab Name as the index... Sheets("Template"). Sometimes ya gotta use Magic number, but use them as soon as possible
Dim Sht As Worksheet
Sheets("Template").Copy After:Sheets(Sheets.Count)
Set Sht =Sheets(Sheets.Count)
Sht.Name = MySheetName
'THereafter use the Sht Object Variable
Sht.Range("A1") = 42
uncertain what is carrying forward from Function vs needing to re-declare variables,
Function can assign values to external variables
Sub A()
X = Make4
End SUb
'--------------------------------------------
Function Make4() As Long
Dim X
X = 2 + 2
Make4 = X
End Function
Subs can set Module and Global scoped Variables
Dim A as String
Private B As Long
Public C As Date
'--------------------------------
Sub Test()
A = "rutyeoi"
B = 42
C = Now
End Sub
Subs and Functions can use external variables
Sub ShowAll()
MsgBox A
MsgBox B
MsgBox C
End Sub
'----------------------------------------
Function BeSquared() As Double
BeSquared = B^2
End Function