2

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
DeanOC
  • 7,142
  • 6
  • 42
  • 56
wannabepudge
  • 23
  • 1
  • 3

1 Answers1

5

At that point CPIDws = CERN000006.

No it doesn't. :)

You've declared CPIDws As Worksheet but you're using it as an argument to the Sheets method, which takes either an index (integer) or name (string) value.

Thus, type mismatch.

Try simply CPIDws.Activate

or, arguably you could do the redundant: Sheets(CPIDws.Name).Activate

THIS may also come in helpful, as it's generally recommended not to rely on Active (cell, sheet, etc.) or Selection when it can be avoided (which is almost always the case, except for some instance when you use the Selection as a method of input. But generally, your macro should probably never need to Select or Activate any cell other than that which the user had selected for input. In your case, since you're beginning at Cells(1,1) and controlling the iteration entirely through code, it's not at all necessary to Select or Activate anything.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Well, I know I had tried CPIDws.Activate at one point and was getting the same error, but as you said, that did work now that I've tried it again. I haven't written VBA in a while, but I have always used *Activate* or *Select.* I will for sure take a look at the thread you referenced. Thanks again for the help! – wannabepudge Mar 25 '15 at 21:42
  • 2
    Beginners seem to use Activate and Selection. All changes can be accomplished without ever activating a sheet. Directly referencing an object is good programming. – usncahill Mar 25 '15 at 22:15