Not duplicate sheet with the same name

asddsaasddas

Board Regular
Joined
Mar 23, 2020
Messages
60
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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?
VBA Code:
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
How about:

VBA Code:
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

Board Regular
Joined
Mar 23, 2020
Messages
60
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
T
How about:

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
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

Board Regular
Joined
Mar 23, 2020
Messages
60
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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.
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Mar 23, 2020
Messages
60
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm not understanding.
This part of the macro
VBA Code:
  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

Board Regular
Joined
Mar 23, 2020
Messages
60
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I'm not understanding.
This part of the macro
VBA Code:
  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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,227
Messages
5,600,414
Members
414,383
Latest member
kevinlarey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top