Rename sheet with if sheet name already exists while looping workbooks-CodePudding

I am running my code trying to loop through old and new formatted workbooks. And the sheet names in my old workbooks are different from the new workbooks.

The code is set to run when the new workbook's names are found.

The old workbooks have sheets named "01", "02" and "03". The new workbooks have sheets named "newname01", "newname02" and "03". The code is set to run to "newname01" and "newname02".

What I need to do is if the code runs through an old workbook, change the old sheet names to the new workbook's sheet names and run the code. And when running through a new workbook, run through it without changing the sheet names.

I tried changing the old workbook's sheet names to the new ones at the beginning of the code. But when the code is running through an old workbook, its sheets don't contain the new names the code shows an error.

I tried using -
If Not______Is Nothing then.

But I couldn't figure out how that code works.

my code--->

Sub CD3()

Dim wb As Workbook

For Each wb In Application.Workbooks


    If Not Application.ActiveProtectedViewWindow Is Nothing Then
            Application.ActiveProtectedViewWindow.Edit
    End If
    
Sheets("newname01").Select
Range("A8:B10").Orientation = 90
Range("C10:D10").Orientation = 90
Range("E8:F10").Orientation = 90
Range("G10:H10").Orientation = 90
Range("I8:J10").Orientation = 90
Range("K10:N10").Orientation = 90
Range("O8:Q10").Orientation = 90
Range("Q8:Q10").FormulaR1C1 = "Observation/ Proposals"

'List Sheet Adding

Sheets.Add After:=Sheets("newname02")
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "03"


'more code


ActiveWindow.Zoom = 75
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("A11").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Next ws

End Sub

When the code is running through an old workbook, it has sheets named "01" and "02". I need to change their name to "newname01" and "newname02" and then run the code.

this is a code I found it changed the code regardles of the name

   Sub RenameSheet()

   Dim Sht   As Worksheet
   Dim NewSht    As Worksheet
   Dim newShtName   As String


   Set NewSht = ActiveSheet


   newShtName = "newname01"

   For Each Sht In ThisWorkbook.Sheets
       If Sht.Name = "newname02" Then
           newShtName = "newname01" & "_" &
    ThisWorkbook.Sheets.Count
       End If
   Next Sht

   NewSht.Name = newShtName

   End Sub

I only need to Change sheet name "01" to "newname01" and "02" to "newname02". And when it already named "newname01" run the rest of the code.

CodePudding user response：

?I tride using -

If Not______Is Nothing then.

I have a feeling that you are not using proper error handling and hence that line or the one before that where you are setting the worksheet is erroring out. Try something like this (UNTESTED)

Option Explicit

Sub Sample()
    Dim wbOld As Workbook
    Dim wbNew As Workbook
    Dim wsOld As Worksheet
    Dim wsNew As Worksheet
    Dim wsName As String
    
    '~~> Change these two as applicable
    Set wbOld = Workbooks("OldWorkBook")
    Set wbNew = Workbooks("NewWorkBook")
    
    '~~> Loop through the worksheets in the old workbook
    For Each wsOld In wbOld.Worksheets
        '~~> Create the name as per new worksheet
        'newname01
        wsName = "newname" & wsOld.Name
                    
        '~~> Attempt to set it. If the worksheet doesn't
        '~~> exists, you will not get an error
        On Error Resume Next
        Set wsNew = wbNew.Sheets(wsName)
        On Error GoTo 0
        
        '~~> Check if the object is not nothing
        If Not wsNew Is Nothing Then
            '~~> Worksheet exists
            '
            '~~> Do what you want
            '
            
            '~~> This is important to prevent false positives
            Set wsNew = Nothing
        End If
    Next wsOld
End Sub

CodePudding user response：

I Wrote two Codes for the two sheet names. The run the code Below

Sub If_Run()


If Not Application.ActiveProtectedViewWindow Is Nothing Then
Application.ActiveProtectedViewWindow.Edit
End If

 'Run_for_newname01() = for workbooks containing a Sheet with "newname01"
 'Run_for_01() = for workbooks containing a Sheet with "01"

    ws = ActiveWorkbook.Worksheets.Count
    For i = 1 To ws
        With ActiveWorkbook.Worksheets(i)
            If .Name Like "*newname01*" Then
            Run_for_newname01
          ElseIf .Name Like "*01*" Then
            Run_for_01
        End If
    End With
Next i

End Sub