Code to duplicate sheets

[VegasNath]

If anybody could assist with the following, I would be extremely grateful

I need to copy ws1 named "1" and create 30 copies in the same wb named 2:31. Then, for each ws in the array of 2:31, I need to unprotect the sheet (no password) and create a formula in C4 of "='1'!C4+1", before re-protecting the sheet. The formula in each sheet needs to be adding 1 to the result of the previous sheet.

Thanks in advance for any help.

[HansV]

Here is a macro you can use or adapt:

Code: Select all

Sub Copy1()
  Dim i As Integer
  Application.ScreenUpdating = False
  For i = 2 To 31
    Worksheets(CStr(i - 1)).Copy After:=Worksheets(Worksheets.Count)
    With Worksheets(Worksheets.Count)
      .Name = CStr(i)
      .Unprotect
      .Range("C4").Formula = "='" & (i - 1) & "'!C4+1"
      .Protect
    End With
  Next i
  Application.ScreenUpdating = True
End Sub

[VegasNath]

Hans, it works like a dream, thankyou very much!!

A small adjustment: How to adjust the formula to "=IF('1'!C4="","",'1'!C4+1)"

[HansV]

Code: Select all

      .Range("C4").Formula = "=IF('" & (i - 1) & "'!C4="""","""",'" & (i - 1) & "'!C4+1)"

(You have to use "" for each " within a quoted string)

[VegasNath]

Many Thanks Hans, Perfect.

I tried recording it (by entering it into a cell and pressing F2), but it uses R1C1 which I always find quite difficult to understand.

[HansV]

In this particular example, the R1C1 formula is very simple because you refer to the same cell on the previous sheet:

Code: Select all

      .Range("C4").FormulaR1C1 = "=IF('" & (i - 1) & "'!RC="""","""",'" & (i - 1) & "'!RC+1)"