工作中会遇到数据汇总,尤其是运营工作,一区二区分别填了之后需要手动复制黏贴好了之后上交,所以写了这个代码,但是还是有很多地方需要改进的,毕竟有些需要手动填的地方,如果有大佬可以指点一下,谢谢
Sub 遍历所有sheet,并匹配填到指定sheet里()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim a, b, c, d As Integer, n As Range, n_in, asc As Integer, arr, st As Worksheet, sht, zb As Worksheet, stc, lc, lb
Set sht = Worksheets("公告数据明细") '手写一下目标sheet
Set zb = Worksheets("总表") '手写一下不想遍历到的sheet
stc = inputbox("请输入数据状态有几列") '需要匹配的数据可能头有好多列,例如区域、城市、经销商名称等,需要手写一下这种东西有多少列
asc = inputbox("请输入需要匹配的列") '需要匹配的列
For Each st In Worksheets
If st.Name <> sht.Name Then
If st.Name <> zb.Name Then
arr = Worksheets(st.Name).Range("A1").CurrentRegion
lc = UBound(arr, 1)
lb = UBound(arr, 2)
For Each n In Worksheets(st.Name).Range(Worksheets(st.Name).Cells(2, stc + 1), Worksheets(st.Name).Cells(lc, lb))
If n <> "" Then
If n <> 0 Then
a = n.Row
For Each n_in In Worksheets(sht.Name).Range(Worksheets(sht.Name).Cells(1, asc), Worksheets(sht.Name).Cells(lc, asc))
If n_in.Value = Worksheets(st.Name).Cells(a, asc).Value Then
c = n_in.Row
End If
Next
b = n.Column
For Each n_in In Worksheets(sht.Name).Range(Worksheets(sht.Name).Cells(1, 1), Worksheets(sht.Name).Cells(1, lb))
If n_in.Value = Worksheets(st.Name).Cells(1, b).Value Then
d = n_in.Column
End If
Next
If n.Value > Worksheets(sht.Name).Cells(c, d).Value Then
Worksheets(sht.Name).Cells(c, d).Value = n.Value
End If
End If
End If
Next n
End If
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "已完成"
End Sub