0

In my excel woorkbook I have 4 Sheets with tab names:
Sheet1
Sheet2
Sheet3
RandomName

The CodeNames for
Sheet1 is Test1A
Sheet2 is Test2A
Sheet3 is Test3A
RandomName is Sheet4

I want the following to happen:

1) Copy all the sheets if tab name contains "Sheet"

2A) Rename all tab names for the copied sheets so that Sheet1 becomes ShA1, copy of Sheet 2 becomes ShA2 and so on..

2B) Also rename all CodeNames for the copied sheets so that the CodeName for ShA1 becomes CName1A, ShA2 becomes CName2A and so on..

So far I have succeeded to copy the sheets and rename the tab names. But if I include my second task which is to rename the CodeNames I get an error:

runtime error 9: Subscript out of range.

What could be wrong?

For "Changing the CodeName part" of the VBAcode i have tried with this, which generates the error:

ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).Name = Replace(ActiveSheet.Name, "?", "?")

"?" = I have tried different thing here

When I have tested the above VBA code for the CodeName, I have place it after the "sh.copy part" of the code (see code below).

Here is the code that works for renaming the tab names (task of renaming CodeNames not included):

Sub FindSheets_and_replace_ShName()
  Const csSheet As String = "Sheet"
  Dim sh As Worksheet

  For Each sh In ActiveWorkbook.Sheets
    If InStr(1, sh.Name, csSheet) <> 0 Then
      sh.copy After:=Sheets(Sheets.Count)

      'ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).Name = Replace(ActiveSheet.Name, "?", "?")

      ActiveSheet.Name = Replace(sh.Name, "eet", "A")
    End If
  Next sh

End Sub
SweDentan
  • 67
  • 8
  • may be [this post](https://stackoverflow.com/questions/31472829/change-sheet-code-name) can help you – DisplayName Apr 14 '19 at 08:47
  • Thank DisplayName ,I have tried the code in the post you linked and tried to implement it in my code, but no luck so far (I'm fairly new to VBA). The code in the post works for changing the codename if I put it in a separate sub and not involving my code: `Sub change_code_name() Dim sheet As Object Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.ActiveSheet.CodeName) sheet.Name = "TestAA" End Sub` But I n – SweDentan Apr 14 '19 at 12:03
  • Also read this: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba, and get rid of your activesheet/workbooks and use propper references – Luuklag Apr 14 '19 at 12:50

0 Answers0