2

Okay, so I recently got into VBA programming and I have been trying to write some code to do the following :

  • Cycle through a column containing True or False Statements (column "K" in my case)
  • If True then gather the corresponding name (column "C") and create a sheet named accordingly

And that's all

So here is the code I wrote :

Sub Generate_Attribute_Table()

    Dim LastRow As Long
    Dim i As Integer
    Dim Nom As String

    LastRow = Range("A1").End(xlDown).Row
    
    For i = 2 To LastRow
        
        If (Cells(i, "K").Value) Then
            
            Nom = Worksheets(1).Cells(i, "C").Value
            ActiveWorkbook.Sheets.Add(After:=Worksheets(Sheets.Count)).Name = Nom
            
        Else
        
            Cells(i, "K").Select
            
        End If
        
    Next i

End Sub

And it seems to work perfectly fine but it stops after generating the first sheet even if there are other True in the column.

The else case is there for debug purposes as I wanted to see what was happening and it confirms that whenever the if statement is verified, the loop stops.

I tried doing the same thing using a "Do Until" loop but it does the same.

What am I missing here ? I couldn't find any answers online so any help would be really nice.

Thanks in advance.

Merlean
  • 51
  • 5
  • 2
    Since your sheet names are in column C it would be more logical to find the lastrow from that column eg. `LastRow = worksheets(1).Cells(Rows.count, "C").End(xlUp).Row` – CDP1802 Jun 07 '21 at 10:30

4 Answers4

2

Per Sheets.Add method documentation.

Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.

You have implicit references to the ActiveSheet so each time you add a new sheet, your code is now referencing the new worksheet.

Add some explicit reference to the worksheet you intend to use, such as:

LastRow = Sheets("Sheet1").Range("A1").End(xlDown).Row

And

Sheets("Sheet1").Cells(i, "K").Value
Samuel Everson
  • 2,097
  • 2
  • 9
  • 24
2

Add Worksheets with Names From a List

  • The following will still raise an error if the worksheet name is invalid.
Option Explicit

Sub Generate_Attribute_Table()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = wb.Worksheets(1) ' wb.Worksheets("Sheet1")
    ' This (usually preferred) way will allow empty cells in the column.
    Dim LastRow As Long: LastRow = sws.Cells(sws.Rows.Count, "A").End(xlUp).Row
    
    Dim dsh As Object
    Dim Nom As String
    Dim i As Long
    
    For i = 2 To LastRow
        ' If you use '(sws.Cells(i, "K").Value)', an error will occur
        ' if there is not a boolean in the cell.
        If sws.Cells(i, "K").Value = True Then
            Nom = sws.Cells(i, "C").Value
            ' Attempt to create a reference to the sheet named 'Nom'.
            Set dsh = Nothing
            On Error Resume Next
            Set dsh = wb.Sheets(Nom)
            On Error GoTo 0
            ' Test for existence.
            If dsh Is Nothing Then ' A sheet named 'Nom' doesn't exist.
                wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Nom
            'Else ' A sheet named 'Nom' already exists.
            End If
        End If
    Next i

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • I was actually going to look for a way to solve this particular issue ! You probably saved me a lot of time, thank you so much ! – Merlean Jun 07 '21 at 11:48
1

Figured it out ! And now I feel really dumb, all I had to do was to be precise about my cell reference. By simply writing :

If (ActiveWorkbook.Sheets(1).Cells(i, "K").Value) Then

It solved everything.

Merlean
  • 51
  • 5
  • 2
    This might be of interest to you... https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – eirikdaude Jun 07 '21 at 10:18
  • 2
    Btw, while using activeworkbook instead of activesheet might be a bit less prone to cause trouble, it is the exact same kind of imprecise referencing as caused the problem in the first place... – eirikdaude Jun 07 '21 at 10:20
  • Dully noted ! Thank you for your insights – Merlean Jun 07 '21 at 11:57
1

Try this:

Sub Generate_Attribute_Table()
Dim LastRow As Long
Dim i As Integer
Dim Nom As String
Dim Sh As Worksheet

Set Sh = ActiveWorkbook.Worksheets("Sheet1")
LastRow = Range("A1").End(xlDown).row

For i = 2 To LastRow
    If Sh.Cells(i, 11).Value = True Then
        Nom = Sh.Cells(i, 3).Value
        ActiveWorkbook.Sheets.Add(After:=Worksheets(Sheets.count)).Name = Nom
    Else
        'Cells(i, 11).Select 'Commented because I don't see why you'd need it
    End If
Next i

End Sub

ferJen
  • 97
  • 9