Not duplicate sheet with the same name

[asddsaasddas]

I have a macro that takes the rows of value in A. What this macro does is that it will create a new sheet with the cell value as its name. For example, column A is populated with values Cat, Dog and Mouse respectively in rows 1,2 and 3. The problem that i face is, if i have a already existing sheet with the name "Cat", "Dog" or "Mouse" an error will be prompted. Is it possible to not duplicate sheets with the same name?

Sub Split_projects()

Dim Splitcode As Range
Sheets("zzMAIN").Select
Set Projects = Range("Projects")

For Each Cell In Projects
Sheets("zTEMPLATE").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = Cell.Value

Next Cell
End Sub

This is my current VBA code for reference.

 

[DanteAmor]

How about:

Sub Split_projects()
  Dim cell As Range, Projects As Range
  Sheets("zzMAIN").Select
  Set Projects = Range("Projects")
  
  For Each cell In Projects
    If Evaluate("ISREF('" & cell.Value & "'!A1)") = False Then
      Sheets("zTEMPLATE").Copy After:=Worksheets(Sheets.Count)
      ActiveSheet.Name = cell.Value
    End If
  Next cell
End Sub

 

[asddsaasddas]

T

How about:

Sub Split_projects()
  Dim cell As Range, Projects As Range
  Sheets("zzMAIN").Select
  Set Projects = Range("Projects")
 
  For Each cell In Projects
    If Evaluate("ISREF('" & cell.Value & "'!A1)") = False Then
      Sheets("zTEMPLATE").Copy After:=Worksheets(Sheets.Count)
      ActiveSheet.Name = cell.Value
    End If
  Next cell
End Sub

Thank you! Its working now. I have a few other problems tho, i'll just post it here and if you're free, do feel free to help me out. Thank you in advanced. Do you have any idea of how not to copy a button over to a new sheet? I have a template that i use for the new sheets created. I have a macro that copies the template over to a new sheet. However, the macro button was also copied over to the new sheet. The last problem i have is, in column A i have 3 values and a "0" in the last row to signify that its the last row. However, when i run the macro, a sheet named "0" was created. Is it possible to count the number of rows in column A and subtract the last row from column A. This will enable my macro to select everything above the row with the "0" and name the range. With this a new page with the name "0" would not be created.

 

[DanteAmor]

Try this

Sub Split_projects()
  Dim cell As Range, Projects As Range
  Dim i As Long, ini As Long, lr As Long, col As Long
  Sheets("zzMAIN").Select
  Set Projects = Range("Projects")
  
  col = Projects.Cells(1).Column
  ini = Projects.Cells(1).Row
  lr = Projects.Cells.Rows.Count + ini - 2
  
  Application.ScreenUpdating = False
  Application.CopyObjectsWithCells = False
  
  For i = ini To lr
    If Evaluate("ISREF('" & Sheets("zzMAIN").Cells(i, col).Value & "'!A1)") = False Then
      Sheets("zTEMPLATE").Copy After:=Worksheets(Sheets.Count)
      ActiveSheet.Name = Sheets("zzMAIN").Cells(i, col).Value
    End If
  Next i
End Sub

 

[asddsaasddas]

T

Thank you! Its working now. I have a few other problems tho, i'll just post it here and if you're free, do feel free to help me out. Thank you in advanced. Do you have any idea of how not to copy a button over to a new sheet? I have a template that i use for the new sheets created. I have a macro that copies the template over to a new sheet. However, the macro button was also copied over to the new sheet. The last problem i have is, in column A i have 3 values and a "0" in the last row to signify that its the last row. However, when i run the macro, a sheet named "0" was created. Is it possible to count the number of rows in column A and subtract the last row from column A. This will enable my macro to select everything above the row with the "0" and name the range. With this a new page with the name "0" would not be created.

Try this

Sub Split_projects()
  Dim cell As Range, Projects As Range
  Dim i As Long, ini As Long, lr As Long, col As Long
  Sheets("zzMAIN").Select
  Set Projects = Range("Projects")
 
  col = Projects.Cells(1).Column
  ini = Projects.Cells(1).Row
  lr = Projects.Cells.Rows.Count + ini - 2
 
  Application.ScreenUpdating = False
  Application.CopyObjectsWithCells = False
 
  For i = ini To lr
    If Evaluate("ISREF('" & Sheets("zzMAIN").Cells(i, col).Value & "'!A1)") = False Then
      Sheets("zTEMPLATE").Copy After:=Worksheets(Sheets.Count)
      ActiveSheet.Name = Sheets("zzMAIN").Cells(i, col).Value
    End If
  Next i
End Sub

Update: I figured out how to not copy the buttons over to a new sheet from a template. I unchecked the "Cut, Copy and Sort inserted objects with their parent cells" checkbox in settings. The only problem now is how do i count the number of rows in a column and subtract 1 row from the total rows in that column. After doing so, how do i select all the rows in the particular column except for the last row and give it a name range.

Sub Set_range_for_projects()

    
'declare variables
Dim startCell As Range
Dim lastRow As Long
Dim ws As Worksheet

'set objects
Set ws = ThisWorkbook.Worksheets("zzMAIN")
Set startCell = Range("AE5")


'Find last row of data
'lastRow = ws.Cells(ws2.Rows.Count -1, startCell.Column).End(xlUp).Row

On Error Resume Next
Set Rng = Range(startCell, Columns(startCell.Column).Find("0")(0))
If Rng Is Nothing Then Set Rng = Range(startCell, Cells(lastRow, startCell.Column))
On Error GoTo 0
Rng.Select
Rng.Name = "Projects"

End Sub

This is my current code, the last value in the row is always "0", However, this code would only work if i manually input "0" for some reason.

 

[DanteAmor]

I figured out how to not copy the buttons over to a new sheet from a template. I unchecked the "Cut, Copy and Sort inserted objects with their parent cells" checkbox in settings. The only problem now is how do i count the number of rows in a column and subtract 1 row from the total rows in that column.

that does the code of post #4

 

[asddsaasddas]

that does the code of post #4

yup, it does solve my problem of the button duplicate. However, it does not solve my problem of selecting all the rows except the last row in a particular column. The last row of the column would always have a "0" as the value. I have tried selecting all the rows above "0" or if "0" is not found, it will select all the rows in the column. However, this will only work if i manually input "0" in any row in the column. It does not take into account the "0" that was derived from another macro for some reason. My solution to this problem is to count the number of rows in a column and subtract 1 from the total numbers of row and thus allowing me to select it and give it a name range. This code was not done in the previous macro button, it was done somewhere else.

Sub Set_range_for_projects()
    
'declare variables
Dim startCell As Range
Dim lastRow As Long
Dim ws As Worksheet

'set objects
Set ws = ThisWorkbook.Worksheets("zzMAIN")
Set startCell = Range("AE5")


'Find last row of data
lastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row

On Error Resume Next
Set Rng = Range(startCell, Columns(startCell.Column).Find(0, lookat:=xlWhole)(0))
If Rng Is Nothing Then Set Rng = Range(startCell, Cells(lastRow, startCell.Column))
On Error GoTo 0
Rng.Select
Rng.Name = "Projects"

End Sub

This is my current VBA code for this macro button

 

[DanteAmor]

I'm not understanding.
This part of the macro

  ini = Projects.Cells(1).Row
  lr = Projects.Cells.Rows.Count + ini - 2

finds the starting row and the ending row of the "projects" range and subtracts one row, the process run from the starting row to the last row - 1 of the "projects" range.

So I don't understand now what you need.

If it's another problem and it's a different macro than your original requirement "Not duplicate sheet with the same name" then I suggest you create another thread.

 

[asddsaasddas]

I'm not understanding.
This part of the macro

  ini = Projects.Cells(1).Row
  lr = Projects.Cells.Rows.Count + ini - 2

finds the starting row and the ending row of the "projects" range and subtracts one row, the process run from the starting row to the last row - 1 of the "projects" range.

So I don't understand now what you need.

If it's another problem and it's a different macro than your original requirement "Not duplicate sheet with the same name" then I suggest you create another thread.

ok Thank you for your help!

 

[DanteAmor]

You are welcome.