Code to duplicate sheets

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Code to duplicate sheets

Post by 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.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to duplicate sheets

Post by 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
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Code to duplicate sheets

Post by 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)"
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to duplicate sheets

Post by 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)
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Code to duplicate sheets

Post by 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.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to duplicate sheets

Post by 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)"
Best wishes,
Hans