用vba连续增加多个有内容的工作表

Question

请以下面要求为例：
原工作薄中只有一个表格sheet1中第一行有6个数字1，2，3，4,5,6。要求：自动生成6个工作表（sht1,sht2,sht3...sht6）,内容应该如下

sht1中两个单元格内容为a1为“工作2室”；a2是公式“=IF(Sheet1!A1=1,"k2",0)”

从sht2开始，有规律地改变单元格中的数字！！！

sht2中两个单元格内容为a1为“工作3室”；a2是公式“=IF(Sheet1!A1=2,"k3",0)”
sht3中两个单元格内容为a1为“工作3室”；a2是公式“=IF(Sheet1!A1=3,"k4",0)”
sht4中两个单元格内容为a1为“工作4室”；a2是公式“=IF(Sheet1!A1=4,"k5",0)”
sht5中两个单元格内容为a1为“工作2室”；a2是公式“=IF(Sheet1!A1=5,"k6",0)”
sht6中两个单元格内容为a1为“工作3室”；a2是公式“=IF(Sheet1!A1=6,"k7",0)”
迎客松01的公式中Formula = "=IF(Sheet1!A1=" & i & ",k" & i + 1 & ",0)"错了

所有自动填充的内容都是有规律的（原来的打错了一点，为方便理解更正如下）
sht2中两个单元格内容为a1为“工作1室”；a2是公式“=IF(Sheet1!A1=2,"我3",0)”
sht3中两个单元格内容为a1为“工作2室”；a2是公式“=IF(Sheet1!A1=3,"我4",0)”
sht4中两个单元格内容为a1为“工作3室”；a2是公式“=IF(Sheet1!A1=4,"我5",0)”
sht5中两个单元格内容为a1为“工作4室”；a2是公式“=IF(Sheet1!A1=5,"我6",0)”
sht6中两个单元格内容为a1为“工作5室”；a2是公式“=IF(Sheet1!A1=6,"我7",0)”

另外，自动生成6个工作表，为什么不是“For i = 1 To 6”而是
“ For i = 1 To Sheets("sheet1").[a65536].End(xlUp).Row
”？
如果是自动生成266个类似的工作表，那么“266”这数字又填在哪里呢？

恳请告知！

Answer 1

A1中的工作室，看不出来什么规律！其它都好办！呵呵！
Sub 新建工作表()

Dim i As Long

For i = 1 To Sheets("sheet1").[a65536].End(xlUp).Row
Sheets.Add after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = "sht" & i
Range("A2").Formula = "=IF(Sheet1!A1=" & i & ",k" & i + 1 & ",0)"
Range("A1") = "工作" & i & "室"
End With
Next i
End Sub

Answer 2

Sub 新建工作表()

Dim i As Long

For i = 1 To 6
Sheets.Add after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = "sht" & i
Range("A2").Formula = "=IF(Sheet1!A1=" & i & ",""我" & i + 1 & """,0)"
Range("A1") = "工作" & i & "室"
End With
Next i
End Sub本回答被提问者采纳

Answer 3

add