Simple Macro to Dupe Sheet and Update Cell Value by +1 [feat. xl2bb mini-sheet]

[k3yn0t3]

Hi all. Could anyone help me with a macro that will allow me to duplicate the sheet shown below and ensure the following:

1. Increase the Factory number in D7 by 1 each time...
2. Rename the sheet based on the number in that cell
   1. i.e., there should be ~200 tabs named "Factory_1", "Factory_2"..."Factory_200" and so on
3. All formulas are included when sheet is copied, and update accordingly (i.e. formulas with "Factory_1" in them update for Factory # of their new sheet)

Thank you,
Sam

Factories_v22.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
7	Factory	1
8
9							Q1	Q1	Q2	Q2	Q2	Q3
10							2	3	4	5	6	7
11							2018	2018	2018	2018	2018	2018
12	Transactions						21,656	27,812	20,327	19,475	23,883	18,354
13
14	Net Sales						586	766	544	508	613	477
15	COGS						-179	-232	-162	-152	-180	-139
16	Gross Profit						406	534	381	357	433	337
17	Labor						-156	-206	-153	-154	-187	-149
18	OpEx						-78	-89	-78	-78	-86	-74
19	Occupancy						-12	-16	-12	-12	-16	-12
20	EBITDA						159	223	137	113	144	102
Factory_1

Cell Formulas
Range	Formula
I12:N12,I14:N14	I12	=+FILTER(INDEX('Factory Database'!$P$8:$MM$109,MATCH(Factory_1!$D$7,'Factory Database'!$A$8:$A$109,0),0),('Factory Database'!$P$6:$MM$6=Factory_1!I$11)*('Factory Database'!$P$5:$MM$5=Factory_1!I$10)*('Factory Database'!$P$7:$MM$7=Factory_1!$C12),0)
I15:N15,I17:N19	I15	=-+FILTER(INDEX('Factory Database'!$P$8:$MM$109,MATCH(Factory_1!$D$7,'Factory Database'!$A$8:$A$109,0),0),('Factory Database'!$P$6:$MM$6=Factory_1!I$11)*('Factory Database'!$P$5:$MM$5=Factory_1!I$10)*('Factory Database'!$P$7:$MM$7=Factory_1!$C15),0)
I16:N16	I16	=+I15+I14
I20:N20	I20	=+SUM(I16:I19)

 

[Armstrong_N14]

How about:
1. Put this code on D7:
=Sheet()

2. add a button and put this code on vba:

Sub DuplicatemySheet()

Dim test As Worksheet
Sheets(1).Copy After:=Sheets(Sheets.Count)
Set test = ActiveSheet
test.Name = "Factory_" & Sheets.Count

End Sub

 

[Michael M]

Maybe this way

Sub MM1()
ans = InputBox("How Many Duplicate Sheets do you want?")
For r = 2 To ans
    With ActiveSheet
        .Copy After:=Sheets(Worksheets.Count)
        Range("D7") = Range("D7").Value + 1
        ActiveSheet.Name = "Factory_" & Range("D7").Value
    End With
Next r
End Sub

 

[k3yn0t3]

Maybe this way

Sub MM1()
ans = InputBox("How Many Duplicate Sheets do you want?")
For r = 2 To ans
    With ActiveSheet
        .Copy After:=Sheets(Worksheets.Count)
        Range("D7") = Range("D7").Value + 1
        ActiveSheet.Name = "Factory_" & Range("D7").Value
    End With
Next r
End Sub

Michael--thank you! Embarrassing question: how do I get this to run? I saved down as a macro-enabled workbook, went to Dev tab, clicked VBA, and added this code to my sheet and saved....

How do i start it?

 

[k3yn0t3]

Michael--thank you! Embarrassing question: how do I get this to run? I saved down as a macro-enabled workbook, went to Dev tab, clicked VBA, and added this code to my sheet and saved....

How do i start it?

Never mind! I ran it! However, while the sheet name is updating, the hardcoded number in each new sheet is always "2" (naming is correct!), but the Factory_1 D7 cell number updates to reflect how many duplicates have been made

 

[k3yn0t3]

Would it be easier to simply make a sheet with all the data and base the macro on that? This is the data to feed into that:

Factories_v23.xlsm
	A	B
2	D7	Sheet Name
3	1	Factory_1
4	2	Factory_2
5	3	Factory_3
6	4	Factory_4
7	5	Factory_5
8	6	Factory_6
9	7	Factory_7
10	8	Factory_8
11	9	Factory_9
12	10	Factory_10
13	11	Factory_11
14	12	Factory_12
15	13	Factory_13
16	14	Factory_14
17	15	Factory_15
18	16	Factory_16
19	17	Factory_17
20	18	Factory_18
21	19	Factory_19
22	20	Factory_20
23	21	Factory_21
24	22	Factory_22
25	23	Factory_23
26	24	Factory_24
27	25	Factory_25
28	26	Factory_26
29	27	Factory_27
30	28	Factory_28
31	29	Factory_29
32	30	Factory_30
33	31	Factory_31
34	32	Factory_32
35	33	Factory_33
36	34	Factory_34
37	35	Factory_35
38	36	Factory_36
39	37	Factory_37
40	38	Factory_38
41	39	Factory_39
42	40	Factory_40
43	41	Factory_41
44	42	Factory_42
45	43	Factory_43
46	44	Factory_44
47	45	Factory_45
48	46	Factory_46
49	47	Factory_47
50	48	Factory_48
51	49	Factory_49
52	50	Factory_50
53	51	Factory_51
54	52	Factory_52
55	53	Factory_53
56	54	Factory_54
57	55	Factory_55
58	56	Factory_56
59	57	Factory_57
60	58	Factory_58
61	59	Factory_59
62	60	Factory_60
63	61	Factory_61
64	62	Factory_62
65	63	Factory_63
66	64	Factory_64
67	65	Factory_65
68	66	Factory_66
69	67	Factory_67
70	68	Factory_68
71	69	Factory_69
72	70	Factory_70
73	71	Factory_71
74	72	Factory_72
75	73	Factory_73
76	74	Factory_74
77	75	Factory_75
78	76	Factory_76
79	77	Factory_77
80	78	Factory_78
81	79	Factory_79
82	80	Factory_80
83	81	Factory_81
84	82	Factory_82
85	83	Factory_83
86	84	Factory_84
87	85	Factory_85
88	86	Factory_86
89	87	Factory_87
90	88	Factory_88
91	89	Factory_89
92	90	Factory_90
93	91	Factory_91
94	92	Factory_92
95	93	Factory_93
96	94	Factory_94
97	95	Factory_95
98	96	Factory_96
99	97	Factory_97
100	98	Factory_98
101	99	Factory_99
102	100	Factory_100
103	101	Factory_101
104	102	Factory_102
105	103	Factory_103
106	104	Factory_104
107	105	Factory_105
108	106	Factory_106
109	107	Factory_107
110	108	Factory_108
111	109	Factory_109
112	110	Factory_110
113	111	Factory_111
114	112	Factory_112
Sheet8

Cell Formulas
Range	Formula
B3:B114	B3	=+"Factory_"&A3
A4:A114	A4	=+A3+1

[/CODE]

 

[Michael M]

The code as provided works fine for me.
All sheets have the correct factory name and each sheet has the correct value in D7
What are you inputting at the Input box question ??
You aren't doing 1 at a time from the original sheet are you ??

 

[Anonymous1378]

I do have the same issue; perhaps a slight modification will suffice? It seems to work for me in this form.

Sub MM1()
ans = InputBox("How Many Duplicate Sheets do you want?")
For r = 2 To ans
    ActiveSheet.Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Range("D7") = r
    ActiveSheet.Name = "Factory_" & r
Next r
End Sub

 

[k3yn0t3]

I do have the same issue; perhaps a slight modification will suffice? It seems to work for me in this form.

Sub MM1()
ans = InputBox("How Many Duplicate Sheets do you want?")
For r = 2 To ans
    ActiveSheet.Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Range("D7") = r
    ActiveSheet.Name = "Factory_" & r
Next r
End Sub

This worked like a charm, Anon. Massive thanks to you & Michael.