New sheet created reference within same code-CodePudding

I'm trying to create a new sheet with a specific name with the following formula:

    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Range("B2") & "_MCT"
    End With

so the new sheet will be named "X_MCT", X being the value in cell B2.

In this same code I'm adding a code that wants to fill this new sheet with values from the original sheet. Is there a way to do this? Below is an example equation I have that's going into the newly created sheet:

Sheets("MCT").Cells(i   6, 2 * j   j - 2   1) = "*FORCES-DEFORMATION FUNCTION    ; Forces-Deformation Function"'''

I've defined the i's and j's but is there a way to change that "MCT" to the new "X_MCT" that the code creates? Or should I replace it with the "Active sheet function" since when I create the new sheet, it becomes the active sheet. My first idea is preferred since it's cleaner.

Below is an example I'd like to make work:

Sub newsheet()

With ThisWorkbook
        Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Range("B2") & "_MCT"
        
       
    End With
    
 Sheets(Range("B2") & "_MCT").Cells(1, 1) = "*FORCES-DEFORMATION FUNCTION    ; Forces-Deformation Function"

End Sub

But I get an "Out of Range error" the text doesn't get written into this new sheet. Only by running the macro again does a new sheet get created with that text, but why doesn't it work on the original sheet?

CodePudding user response：

Try this:

Sub NewSheet()
  With ThisWorkbook.Sheets
    Dim Reference as Worksheet
    Set Reference = .Item("A worksheet with B2")
    With .Add(After:=.Item(.Count))
      .Name = Reference.Range("B2") & "_MCT"
      .Cells(1, 1) = "*FORCES-DEFORMATION FUNCTION    ; Forces-Deformation Function"       
    End with
  End With
End Sub

You got an Out of range error because of Sheets(Range("B2") & "_MCT") on the last line of your code. Range("B2") in this case refers to the newly created sheet and is empty. Therefore, this code is trying to access the "_MCT" sheet, which, I guess, doesn't exist. That's where Subscript out of range happens.