Hi everyone. In Sheet1 of my workbook, for each unique cell values under Column M (those values are 'alphabets', e.g. "A", "B", "C", etc.), I want to create new Worksheets titled "Class [cell value]", then copy the Sheet1 rows containing the relevant cell values ('alphabets') to the corresponding new worksheets. The problem I am facing now is that I end up creating a single new worksheet titled "Class ". Would appreciate if anyone can help.
Code
Private Sub Test_Click()Dim Ws As Worksheet
Dim i As Integer
Dim ClassRange As Range
Dim ClassCell As Range
Set ClassRange = Sheet1.Columns("M")
For Each c In ClassRange
For Each Ws In Worksheets
If LCase(Ws.Name) = ClassString Then
Sheet1.Range("Q1").Value = "Sheet Found"
Exit For
Else
Sheet1.Range("Q1").Value = "Class " & c.Text
End If
Next Ws
If Sheet1.Range("Q1").Value = "Sheet Found" Then
Sheet1.Select
ElseIf InStr(1, Sheet1.Range("Q1").Text, "Class", vbTextCompare) = 1 Then
ActiveWorkbook.Sheets.Add Before:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheet1.Range("Q1").Text
Sheet1.Select
Else
MsgBox ("Error!")
Exit For
End If
Next c
End Sub
Display More