Assigning variable to worksheet: VBA

Question

I am trying to assign worksheet reference using a variable integer. To provide context, I imported data, split data into different worksheets based on a date criteria and then name the sheets using the month and year.

I later want to be able to compare data within the different sheets but the trick is I will never know what the date range is until the data is imported. The only differentiation would be the integer variable as I am creating the sheets. Below I have copied parts of the code that relate to my issue.

Dim WS, WS1, WS2, WS3 As Worksheets
Dim Sheetname As String
Dim p, q, y As Integer

p = Worksheets.Count
For q = 1 To p
    With Worksheets(q)
        Sheetname = Format(st_date, "yyyy-mmm")
        ActiveSheet.Name = Sheetname
    End With
    If q = 1 Then
       Set WS1 = ThisWorkbook.Sheets(Sheetname)
    End If
    If q = 2 Then
       Set WS2 = ThisWorkbook.Sheets(Sheetname)
    End If
    If q = 3 Then
       Set WS3 = ThisWorkbook.Sheets(Sheetname)
    End If
Next q

When I run the program I get a "type mismatch" error when it has to set WS3. I'm not really sure why it only does that when it gets to 3.

Answer 1

The reason you only get a type mismatch error on WS3 is because of how you have defined WS1, WS2 and WS3. The line

Dim WS, WS1, WS2, WS3 As Worksheets

is equivalent to the line

Dim WS As Variant, WS1 As Variant, WS2 As Variant, WS3 As Worksheets

Issues:

1. You didn't mean to declare most of those objects as variants
2. Because they are declared as variants, they can happily be assigned to worksheet objects in your loop without error
3. Worksheets is the wrong object type, you want to use a Worksheet object.
4. Because (only) WS3 is the wrong type of object, it throws a type mismatch error!

The code should be:

' Declare variable types individually
Dim WS As Worksheet, WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim Sheetname As String
' Longs can be bigger than Integers, unless memory is a big issue can use Longs by default
' Also declare individually
Dim p As Long, q As Long, y As Long

' Fully qualifying the sheets by using a workbook object
p = ThisWorkbook.Worksheets.Count
For q = 1 To p
    ' You never changed st_date, so are trying to name all sheets the same
    ' st_date = ...
    Sheetname = Format(st_date, "yyyy-mmm")
    ' The active sheet doesn't change, no need for With block for single statement
    ThisWorkbook.Worksheets(q).Name = Sheetname
    ' Use ElseIf statements for quicker evaluation and neater code
    If q = 1 Then
       Set WS1 = ThisWorkbook.Sheets(Sheetname)
    ElseIf q = 2 Then
       Set WS2 = ThisWorkbook.Sheets(Sheetname)
    ElseIf q = 3 Then
       Set WS3 = ThisWorkbook.Sheets(Sheetname)
    End If
Next q

Improvement

I'm not really sure why you assign the sheet name according to sheet q, but then use If logic to assign the relevant sheets by name. You could probably replace the code with something like

Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim Sheetname As String
Dim p As Long, q As Long,  
p = ThisWorkbook.Worksheets.Count
For q = 1 To p
    ' You never changed st_date, so are trying to name all sheets the same
    ' st_date = ...
    Sheetname = Format(st_date, "yyyy-mmm")
    ThisWorkbook.Worksheets(q).Name = Sheetname
Next q
Set WS1 = ThisWorkbook.Sheets(1)
Set WS2 = ThisWorkbook.Sheets(2)
Set WS3 = ThisWorkbook.Sheets(3)

Answer 2

Try it like this:

Sub TestMe()

    Dim WS          As Worksheets, WS1 As Worksheets, WS2 As Worksheets, WS3 As Worksheets
    Dim Sheetname   As String
    Dim p&, q&, y&  'shortwriting for dim q as long

    For q = 1 To Worksheets.Count            
        With Worksheets(q)
            .name = Format(st_date, "yyyy-mmm") & q
        End With            
        If q = 1 Then
           Set WS1 = ThisWorkbook.Worksheets(Sheetname)
        End If            
        If q = 2 Then
           Set WS2 = ThisWorkbook.Worksheets(Sheetname)
        End If            
        If q = 3 Then
           Set WS3 = ThisWorkbook.Worksheets(Sheetname)
        End If            
    Next q            
End Sub

I have changed With Worksheet.name, thus it is useful. And I have added &q to it, just to make sure the names are always different. The Dims are changed as per the idea in the comments.