|
求大神帮忙,如果工作表标签的名字为2时,其后面的明细表为2.1、2.2 、2.3。。。。,但sheetname为2时,后面只有1张表,代码就出错,求大神帮解决一下小弟的问题,还有本人的代新学码确实拿不出手,希望大神能帮忙优化,谢谢大神
Sub 编号()
Dim arr, rng As Range, i&, str As String, j&, m, n, x, k As Integer
n = ActiveSheet.Range("a65536").End(xlUp).Row
For x = 5 To n
arr = ActiveSheet.Range(ActiveSheet.Cells(5, "b"), ActiveSheet.Cells(n, "b"))
Next x
For i = 1 To n - 4
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "1." & Format(i, "0") & "、" & arr(i, 1)
If ActiveSheet.Name = "汇总" Then
Worksheets(ActiveSheet.Index + i).Name = Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "2" Then
Worksheets(ActiveSheet.Index + i).Name = "2." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "2." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "3" Then
Worksheets(ActiveSheet.Index + i).Name = "3." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "3." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "4" Then
Worksheets(ActiveSheet.Index + i).Name = "4." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "4." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "5" Then
Worksheets(ActiveSheet.Index + i).Name = "5." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "5." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "6" Then
Worksheets(ActiveSheet.Index + i).Name = "6." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "6." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "7" Then
Worksheets(ActiveSheet.Index + i).Name = "7." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "7." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "8" Then
Worksheets(ActiveSheet.Index + i).Name = "8." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "8." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "9" Then
Worksheets(ActiveSheet.Index + i).Name = "9." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "9." & Format(i, "0") & "、" & arr(i, 1)
ElseIf ActiveSheet.Name = "10" Then
Worksheets(ActiveSheet.Index + i).Name = "10." & Format(i, "0")
Worksheets(ActiveSheet.Index + i).[A2] = "名称:" & "10." & Format(i, "0") & "、" & arr(i, 1)
Else
Worksheets(ActiveSheet.Index + i).Name = "1." & Format(i, "0")
End If
Next i
k = 4
For j = ActiveSheet.Index + 1 To ActiveSheet.Index + n - 4
k = k + 1
str = "含税总计"
Set rng = Worksheets(j).Cells.Find(str, lookat:=xlWhole)
Worksheets(ActiveSheet.Index).Range("G" & k).Formula = "=" & Worksheets(j).Name & "!" & Range(rng.Address).Offset(0, 5).Address(0, 0)
Worksheets(ActiveSheet.Index).Range("L" & k).Formula = "=" & Worksheets(j).Name & "!" & Range(rng.Address).Offset(0, 10).Address(0, 0)
Range("n5") = "=ROUND((G5-L5),0)"
Range(ActiveSheet.Cells(5, "n"), ActiveSheet.Cells(n, "n")).FillDown
Next j
End Sub
|
|