VBA is overwriting master sheet.

[rikvny02]

The code below creates a new sheet based on a list of data on the options tab. The code creates each sheet (great). To my knowledge if the code is used a second time around, it should look for sheets that don't exist and then add only new sheets. The code does not add a new sheet if a new row is added on the options tab. To make it worse the code overwrites the options tab the second go around. Lastly. the code seems to run very slow.

 

[StephenCrump]

Your .Copy statement is outside your If/Endif blocks. So it's going to happen on every iteration of the loop.

This will overwrite whatever the ActiveSheet is at the time, which is presumably why "options" is being overwritten. This could also be a reason your code is running slow.

The code does not add a new sheet if a new row is added on the options tab.

I'm not clear what this means?

So that we can test your code (no-one is going to retype from your picture), please post next time using code tags:

 

[rikvny02]

You were absolutely correct. Moved that line of code and everything works great. Thank you.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Range(Sheets("options").[e5], Sheets("options").Cells(Rows.Count, "e").End(xlUp))

For Each MyCell In MyRange
If Len(MyCell.Text) > 0 Then
'Check if sheet exists
If Not SheetExists(MyCell.Value) Then
Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Worksheets("td32").Cells.Copy ActiveSheet.Range("a1")
End If
End If

Next MyCell
End Sub

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet

If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing
End Function

 

[StephenCrump]

You were absolutely correct. Moved that line of code and everything works great. Thank you.

That's good to hear, thanks for posting back.

 

[smozgur]

You were absolutely correct. Moved that line of code and everything works great. Thank you.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[SunnyAlv]

May I know the type of text used in VBA, please? because it's really cool @rikvny02

 

[rikvny02]

May I know the type of text used in VBA, please? because it's really cool @rikvny02

I seen someone else using it too and just had to have it. I actually think it makes things easier to read and understand.