求一个vba小程序,谢谢
答案:3 悬赏:0
解决时间 2021-01-29 07:38
- 提问者网友:暮烟疏雨之际
- 2021-01-28 09:17
求一个vba小程序,谢谢
最佳答案
- 二级知识专家网友:野慌
- 2021-01-28 09:51
Sub 合并至总表()
Dim Rng As Range, Rng1 As Range, Rng2 As Range, Sh As Worksheet
Dim Dic As Object, i1&, j1&, i2&, j2&, K1$, K2$, K3$, i0&
K1 = "水果": K2 = "其他1": K3 = "其他2"
Set Dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each Sh In Worksheets
Dic(Sh.Name) = ""
Next Sh
If Not Dic.exists("总表") Then
Worksheets.Add before:=Worksheets(1)
ActiveSheet.Name = "总表"
Else
Worksheets("总表").Cells.ClearContents
End If
i0 = 0
With Worksheets("总表")
For Each Sh In Worksheets
If Sh.Name <> .Name Then
Set Rng = Sh.Cells.Find(what:=K1)
'总表采用第一个分表的两行表头
If (Not Rng Is Nothing) And i0 = 0 Then
Rng.Resize(2, 9).Copy .Cells(1, 1)
i0 = 2
j1 = 3: j2 = 3
End If
Set Rng1 = Sh.Cells.Find(what:=K2)
Set Rng2 = Sh.Cells.Find(what:=K3)
If Rng Is Nothing Or Rng1 Is Nothing Or Rng2 Is Nothing Then
Else
Set Rng = Rng.Offset(i0, 0)
.Cells(j1, 1).Resize(Rng1.Row - Rng.Row, 5) = Rng.Resize(Rng1.Row - Rng.Row, 5).Value
.Cells(j2, 6).Resize(Rng2.Row - Rng.Row, 4) = Rng.Offset(0, 5).Resize(Rng2.Row - Rng.Row, 4).Value
j1 = j1 + Rng1.Row - Rng.Row
j2 = j2 + Rng2.Row - Rng.Row
End If
End If
Next Sh
.Cells(1, 1).Resize(Application.WorksheetFunction.Max(j1, j2) - 1, 9).Borders.LineStyle = xlContinuous
End With
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set Dic = Nothing
End Sub
追问:你果然是大神,666
Dim Rng As Range, Rng1 As Range, Rng2 As Range, Sh As Worksheet
Dim Dic As Object, i1&, j1&, i2&, j2&, K1$, K2$, K3$, i0&
K1 = "水果": K2 = "其他1": K3 = "其他2"
Set Dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each Sh In Worksheets
Dic(Sh.Name) = ""
Next Sh
If Not Dic.exists("总表") Then
Worksheets.Add before:=Worksheets(1)
ActiveSheet.Name = "总表"
Else
Worksheets("总表").Cells.ClearContents
End If
i0 = 0
With Worksheets("总表")
For Each Sh In Worksheets
If Sh.Name <> .Name Then
Set Rng = Sh.Cells.Find(what:=K1)
'总表采用第一个分表的两行表头
If (Not Rng Is Nothing) And i0 = 0 Then
Rng.Resize(2, 9).Copy .Cells(1, 1)
i0 = 2
j1 = 3: j2 = 3
End If
Set Rng1 = Sh.Cells.Find(what:=K2)
Set Rng2 = Sh.Cells.Find(what:=K3)
If Rng Is Nothing Or Rng1 Is Nothing Or Rng2 Is Nothing Then
Else
Set Rng = Rng.Offset(i0, 0)
.Cells(j1, 1).Resize(Rng1.Row - Rng.Row, 5) = Rng.Resize(Rng1.Row - Rng.Row, 5).Value
.Cells(j2, 6).Resize(Rng2.Row - Rng.Row, 4) = Rng.Offset(0, 5).Resize(Rng2.Row - Rng.Row, 4).Value
j1 = j1 + Rng1.Row - Rng.Row
j2 = j2 + Rng2.Row - Rng.Row
End If
End If
Next Sh
.Cells(1, 1).Resize(Application.WorksheetFunction.Max(j1, j2) - 1, 9).Borders.LineStyle = xlContinuous
End With
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set Dic = Nothing
End Sub
追问:你果然是大神,666
全部回答
- 1楼网友:醉吻情书
- 2021-01-28 10:20
可以帮你写 私信我
- 2楼网友:往事隔山水
- 2021-01-28 10:07
文件呢?
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯