Everything I've read shows that I'm correctly denoting my variable and calling the sheet I want to activate. The last line is where I am getting the type mismatch. At that point CPIDws = CERN000006. I read somewhere that it might be problematic that the name is letters and numbers, but haven't found a way around it.
Sub Create_tab()
Dim newWS As Worksheet, CernWS As Worksheet, CPID As Variant
Dim Template As Worksheet, CPIDclm As Long, CERNdata As Range, CPIDcheck As Variant
Dim lngRow As Long, lngCol As Long, i As Integer, CPIDws As Worksheet
Set Template = Sheets("Template")
Set CernWS = Sheets("CERN ID's")
'Set lngRow = 1
'Set lngCol = 1
CernWS.Activate
Cells(1, 1).Select
Do
ActiveCell.Offset(1, 0).Select
Set CPID = ActiveCell
'create a new sheet as a copy of the template
Sheets("Template").Copy _
after:=ActiveWorkbook.Sheets(ThisWorkbook.Sheets.Count)
'Name the new sheet as the current CPID value from CERN ID's worksheet
ActiveSheet.Name = CPID
Set CPIDws = ActiveSheet
'interigate AAA Data and update the new sheet with the data specific to the current cpid
Sheets("AAA Data").Activate
Cells(2, 3).Activate
Set CPIDcheck = ActiveCell
Do
If CPID = CPIDcheck Then
ActiveCell.Offset(0, -2).Select
Set CERNdata = Range(Selection, Selection.End(xlToRight))
End If
Sheets(CPIDws).Activate