VBA Excel Autoincrement the string value when looping

Question

I have got a list including the number of flats.

which I want to have populated in the string here

now I would like to have it incremented by 1 for every sheet I am going to generate by the loop as below:

 Sub otdr()

 Dim i As Long
 Dim xNumber As Long
 Dim otdr As Range
 Dim xName As String
 Dim ws As Worksheet

 Application.ScreenUpdating = False

 Set ws = Sheets("OTDR TRACE - 1")
 Set otdr = ws.Range("Q46")
 xNumber = Sheets("Frontsheet").Range("D32").Value


 For i = 1 To (xNumber - 1)
 otdr = "OT " & (i + 1) & " of " & Number
 ws.Copy After:=ActiveWorkbook.Sheets(ws.Index + i - 1)
 ActiveSheet.Name = "OTDR TRACE - " & (i + 1)
 Next

 ws.Activate
 otdr = "OT 1 of " & Number

 Application.ScreenUpdating = True

 End Sub

after implementation of the existing code, it stopped work. I created the 2nd loop, but the string is copied, but without changing the numbers.

  Dim i As Long, j As Long
  Dim xNumber As Long, yNumber As Long
  Dim otdr As Range, desc As Range
  Dim xName As String
  Dim ws As Worksheet, wk As Worksheet

 Application.ScreenUpdating = False

 Set ws = Sheets("OTDR TRACE - 1")
 Set wk = Sheets("Fibre drop release sheet")
 Set otdr = ws.Range("Q46")
 Set desc = ws.Range("N7")
 xNumber = Sheets("Frontsheet").Range("D32").Value
 yNumber = Sheets("Fibre drop release sheet").Range("E3").Value

 For i = 1 To (xNumber - 1)
 otdr = "OT " & (i + 1) & " of " & xNumber
 ws.Copy After:=ActiveWorkbook.Sheets(ws.Index + i - 1)
 ActiveSheet.Name = "OTDR TRACE - " & i + 1
 Next

 For j = 1 To Number
 desc = "FIBRE TRACE @ 1310 & 1550 - F1 - Flat " & j    ' copying only the string without the increment numeration
 ws.Copy After:=ws.Range("N7")(ws.Index + i) ' this line generates an error

 Next


 ws.Activate
 otdr = "OT 1 of " & xNumber

Once added

    ws.Copy After:=ws.Range("N7")(ws.Index + i)

I am getting the following error:

Method 'Copy of Object' worksheet failed

The solution below:

Method 'Copy' of object '_Worksheet' failed

wasn't helpful for me

Is there a chance to have this number incremented by sheet?

UPDATE:

Now my code looks like this:

  Sub otdr()

  Dim i As Long, j As Long, Lastrow As Long
  Dim xNumber As Long, yNumber As Long
  Dim otdr As Range, desc As Range
  Dim xName As String
  Dim ws As Worksheet, wk As Worksheet

  Application.ScreenUpdating = False

  Set ws = Sheets("OTDR TRACE - 1")
  Set wk = Sheets("Fibre drop release sheet")
  Set otdr = ws.Range("Q46")
  Set desc = ws.Range("N7")
  xNumber = Sheets("Frontsheet").Range("D32").Value
 
  Lastrow = wk.Cells(wk.Rows.Count, "E").End(xlUp).Row

  For i = 1 To (xNumber - 1)
  otdr = "OT " & (i + 1) & " of " & Number
  ws.Copy After:=ActiveWorkbook.Sheets(ws.Index + i - 1)

  ActiveSheet.Name = "OTDR TRACE - " & i + 1

  Next

  For j = 1 To Lastrow
  desc = "FIBRE TRACE @ 1310 & 1550 - F1 - Flat " & j

  Next


  ws.Activate
  otdr = "OT 1 of " & Number

  Application.ScreenUpdating = True



  End Sub

UPDATE II

For the following situation:

   For i = 1 To (xNumber - 1)
   otdr = "OT " & (i + 1) & " of " & Number
   ws.Copy After:=ActiveWorkbook.Sheets(ws.Index + i - 1)

   ActiveSheet.Name = "OTDR TRACE - " & i + 1
   For j = 1 To Lastrow
   desc = "FIBRE TRACE @ 1310 & 1550 - F1 - Flat " & j
   Worksheets("OTDR TRACE - 1").Range("N7").Copy Destination:=Sheets("OTDR TRACE - " & j + 1).Range("N7")

   Next

 Next

I am getting "We can't do it for a merged cells" for the following line:

      Worksheets("OTDR TRACE - 1").Range("N7").Copy Destination:=Sheets("OTDR TRACE - " & j + 1).Range("N7")

Answer 1

You have a typo in your code. Your second loop is, I think, supposed to say:

For j = 1 To yNumber

Do you have:

Option Explicit

at the top of your module? If not, add it (and get in the habit of doing so), then VBA will require you to declare your variables and throw an error for variable typos (where the typo results in a variable that has not been declared).

I'm then unsure of the intention of the line that is failing:

ws.Copy After:=ws.Range("N7")(ws.Index + i)

But it's certainly not correct. If you are trying to set the value of Range("N7") in one of the worksheets, then you should do that directly, not using ws.Copy. If that is what you're trying to do, then the syntax is like this:

Worksheets("<name>").Range("<range>").Value = <value>

Edit, 2020/12/08:

Further to your comments and clarification, you're still trying to do the copy and paste in one go, which is why you're having issues debugging it. Split the line that is failing into two using a variable (declare it at the top though):

Dim strValue As String ' Or whichever type is appropriate

...

strValue = Worksheets("OTDR TRACE - 1").Range("N7").Value
Sheets("OTDR TRACE - " & j + 1).Range("N7").Value = strValue

I suspect that you will still get the merged cells error when trying to set the value, but then you are clear what the issue is and can work out a solution. I'm not on a machine with Excel at the moment, but you may find that you need to use the other typical way of grabbing the cell value which is:

Cells(row, col)

so, in your case:

Worksheets("OTDR TRACE - 1").Cells(7, 14).Value