VBA - create multiple duplicate sheets

suesaid

New member
Joined
Aug 30, 2016
Messages
3
Reaction score
0
Points
0
I would like to create multiple copies of a sheet in a workbook, and each sheet will have a particular cell that changes according to a column in a separate sheet.

For example, sheet 1 contains data in the range A1:A5
Sheet 2 contains completely separate and fixed information. However, in sheet 2, Cell B2='Sheet 1'!A1
I would like to create sheets 3,4,5,6,7,8 which are exactly the same as sheet 2, except that B2=A2 in sheet 3, B2=A3 in sheet 4, ect.

I know how to create duplicate sheets, but i do not know how to write the code to change the formula B2='Sheet 1'!A1 to B2='Sheet 1'!An+1 starting from n=1.

Any ideas?

Thanks.
 
Post your current code and we'll tweak it.
 
Code:
[FONT=Menlo][COLOR=#011993]Sub Copier()
Dim x As Integer

x = InputBox("Enter number of times to copy active sheet")
For numtimes = 1 To x
  ActiveWorkbook.ActiveSheet.Copy After:=ActiveWorkbook.Sheets("Sheet1")

Next


Dim OriginalText As String
Dim CorrectedText As String
Dim i As Integer


i = [0,4]


OriginalText = Range("F4").Value
'F4 ='Sheet 1'!Ai


CorrectedText = Replace(OriginalText, "i", "i+1")


End Sub
[/COLOR][/FONT]



This code works to duplicate the sheet, but nothing is replaced..
 
Last edited by a moderator:
try:
Code:
Sub Copier()
x = InputBox("Enter number of times to copy active sheet")
Set SceSht = ActiveSheet
For numtimes = 1 To x
  SceSht.Copy After:=ActiveWorkbook.Sheets("Sheet1")
  ActiveSheet.Range("B2").Formula = "=Sheet1!A" & numtimes
Next
End Sub
 
Back
Top