loop through each cell in range not looping through whole range

[josvajensen]

My code is supposed to create a new sheet for each entry that is marked in a list of task codes.

This is how it is supposed to function.

In my workbook I have a list of task codes. For each task code I've left a space for the user to indicate which task codes the user wants to create a sheet for. The code is supposed to loop through a range and check if each cell has been marked. If the cell is marked, then the code continues to create a sheet for that task code as long as that sheet doesn't already exist.

Currently my code will only create new sheets for a portion of the tasks that are marked. I have attempted three different ways of selecting the range and looping through each cell in that range and no version includes all marked task codes.


I've checked using msgbox to make sure that the entire range I want is indeed being selected. Somehow, though, in a list of 16 task codes I want to include, only 4 were created.

Please help!

Dim rng1 As Range
Dim cell1 As Range
Dim i As Long
Dim shName As String
Dim exists As Boolean

'determine range of marked task codes from column C
Set rng1 = Worksheets("Task Codes").Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)

For Each cell1 In rng1
If cell1 <> "" Then
'sheet names can only be 31 characters, so i'm only using the first 31 characters of a task code _
to name the new sheet
shName = left(cell1.Offset(0, 3).Value, 31) 'list of task code names is 3 columns over

For i = 1 To Sheets.Count

If Sheets(i).Name = shName Then
exists = True
End If
Next i

If Not exists Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = shName
Call FormatNewSheet 'adds some things to the new sheet
End If
End If
Next cell1

 

[Alex Blakenburg]

Thank you for the detail in the feedback. Glad we could help.

 

[6StringJazzer]

deleted