Checking worksheet names and renaming if duplicate found

VBAno0b · Dec 10th 2010

Hi everyone! I do not have much experience in excel and would appreciated any help with my situation. My problem in excel right now is checking all the worksheet names and if one exists, then rename the current worksheet to WorksheetName1, with the next one being WorksheetName2, WorksheetName3 and so on.

For example, right now, I name my worksheets as "Imported" using the following:

Code

Range("O1").Select
    Selection.Formula = "Imported"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Selection.Columns.AutoFit
    ActiveSheet.Name = Range("O1").Value
    Range("O1").Value = ""

But I want to modify the above so it checks worksheet names already in the excel workbook and if another worksheet is already named "Imported", then the current worksheet will be renamed "Imported1".

Also, my codes following that also functions based on the worksheet names. For example, in another Sub, I add another worksheet named "Graphs" and have it behind "Imported". But how do I make sure the data used to make "Graphs1" will come from "Imported1" (if "Imported" already exists). Code below:

Code

Sub GenerateChart()
Dim x As Long
Dim UnknownCount, YesCount, Yes7Count, Yes8Count, ExemptCount, Exempt7Count, Exempt8Count, BlankCount As Integer
Dim RngToCover As Range
Dim ChtOb As ChartObject
 
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Graphs"
 
    ActiveSheet.Range("B2").Select
    ActiveCell.FormulaR1C1 = "Unknown"
    ActiveSheet.Range("B3").Select
    ActiveCell.FormulaR1C1 = "Yes"
    ActiveSheet.Range("B4").Select
    ActiveCell.FormulaR1C1 = "Yes-7"
    ActiveSheet.Range("B5").Select
    ActiveCell.FormulaR1C1 = "Yes-8"
    ActiveSheet.Range("B6").Select
    ActiveCell.FormulaR1C1 = "Exempt"
    ActiveSheet.Range("B7").Select
    ActiveCell.FormulaR1C1 = "Exempt-7"
    ActiveSheet.Range("B8").Select
    ActiveCell.FormulaR1C1 = "Exempt-8"
    ActiveSheet.Range("B9").Select
    ActiveCell.FormulaR1C1 = "Blank"
 
With Sheets("Imported")
x = Range("G" & Rows.Count).End(xlUp).Row
UnknownCount = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Unknown")
YesCount = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Yes")
Yes7Count = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Yes-7")
Yes8Count = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Yes-8")
ExemptCount = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Exempt")
Exempt7Count = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Exempt-7")
Exempt8Count = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "Exempt-8")
BlankCount = Application.WorksheetFunction.CountIf(Range("G3:G" & x), "")
End With
 
ActiveSheet.Range("C2").Formula = UnknownCount
ActiveSheet.Range("C3").Formula = YesCount
ActiveSheet.Range("C4").Formula = Yes7Count
ActiveSheet.Range("C5").Formula = Yes8Count
ActiveSheet.Range("C6").Formula = ExemptCount
ActiveSheet.Range("C7").Formula = Exempt7Count
ActiveSheet.Range("C8").Formula = Exempt8Count
ActiveSheet.Range("C9").Formula = BlankCount
 
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("B2:C9")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs"
ActiveChart.ApplyDataLabels
ActiveChart.Legend.Delete
Set RngToCover = ActiveSheet.Range("E2:L16")
Set ChtOb = ActiveChart.Parent
ChtOb.Height = RngToCover.Height ' resize
ChtOb.Width = RngToCover.Width ' resize
ChtOb.Top = RngToCover.Top ' reposition
ChtOb.Left = RngToCover.Left ' reposition
 
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Range("B2:C9")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs"
Set RngToCover = ActiveSheet.Range("E18:L32")
Set ChtOb = ActiveChart.Parent
ChtOb.Height = RngToCover.Height ' resize
ChtOb.Width = RngToCover.Width ' resize
ChtOb.Top = RngToCover.Top ' reposition
ChtOb.Left = RngToCover.Left ' reposition
 
 
End Sub

Display More

I know the code is probably really inefficient and looks horrible, but I just need to get it done. Inefficiency is the least of my concerns

P.S. Extremely noob question: How do I automatically format the codes so it indents properly? And when an error occurs, how do I check which line is the problem? Thanks in advance!

VBAno0b · Dec 10th 2010

Re: Checking worksheet names and renaming if duplicate found

Does anyone know?...

junho lee · Dec 11th 2010

Re: Checking worksheet names and renaming if duplicate found

Hi VBAno0b,
Nice to meet you.

Code

Dim ws As Worksheet
Dim chk As Boolean
Dim cnt As Integer
For Each ws In ThisWorkbook.Worksheets
If InStr(ws.Name, "Imported") > 0 Then
chk = True
cnt = cnt + 1
End If
Next
If chk Then
Sheets.Add.Name = "Imported" & cnt
Else
Sheets.Add.Name = "Imported"
End If

Display More

There is Smart Indent belowpage.
http://www.oaltd.co.uk/Indenter/IndentFrm.htm
Regards, junho

AAE · Dec 11th 2010

Re: Checking worksheet names and renaming if duplicate found

VBANoOb,

Cross posting without providing a link to the other post is a violation of the Forum Rules you agreed to abide by. We prefer you do not cross post, but if you do, you are required to provide a link.

You cross posted at Excel Help Forum: Checking worksheet names and renaming if duplicate found

To understand why cross posting is not acceptable read in full Message to Cross Posters which you can access via my signature.
Future violations of this rule will result in your thread being deleted or locked without notice.

VBAno0b · Dec 13th 2010

Re: Checking worksheet names and renaming if duplicate found

Sorry, didn't realize it was the same forum..

Thanks for responding junho lee, however, i get an error on this line:

Code

For Each ws In ThisWorkbook.Worksheets

yegarboy · Dec 13th 2010

Re: Checking worksheet names and renaming if duplicate found

http://www.ozgrid.com/forum/showthread.php?t=147533 Is this what your looking for?

VBAno0b · Dec 13th 2010

Re: Checking worksheet names and renaming if duplicate found

I have changed the code drastically now. I'm not sure if the code will check for the largest number of Imported (ie. find Imported2 instead of Imported1).. But it now looks like this:

Code

Sub Test()
Dim sht As Worksheet, flg As Boolean, counter As Integer
For Each sht In Worksheets
If sht.Name Like "Imported*" Then flg = True: Exit For
Next
If flg = True Then
    'when Imported* is found, this will run
    'taking the worksheet name and inserting it in cell P1
    Range("P1").Formula = "=MID(CELL(""filename"",Z1),FIND(""]"",CELL(""filename"",Z1))+1,255)"
    'taking the last character of string in P1
    Range("Q1").Formula = "=RIGHT(P1,1)"
    'storing value in Q1 into EndNumber
    EndNumber = Range("P1").Value
    'adding 1 to EndNumber
    counter = EndNumber + 1
    'combining Imported with counter to get name for new worksheet
    Range("O1").Select.Formula = "=Imported" & counter
    'copy and pasting the name into worksheet tab
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Selection.Columns.AutoFit
    ActiveSheet.Name = Range("O1").Value
    'erasing all values in worksheet
    Range("O1").Value = ""
    Range("P1").Value = ""
    Range("Q1").Value = ""
Else
    'when Imported* is NOT found, this will run
    'specifying cell O1 as Imported1 (name of worksheet)
    Range("O1").Formula = "Imported1"
    'copy and pasting the name into worksheet tab
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Selection.Columns.AutoFit
    ActiveSheet.Name = Range("O1").Value
    'erasing the value in cell O1
    Range("O1").Value = ""
End If
End Sub

Display More

However, I get an error on this line:

Code

Range("O1").Select.Formula = "=Imported" & counter

I don't understand why this won't run?

Checking worksheet names and renaming if duplicate found

Participate now!

Share