|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- arr = Array("姓名", "期初余额", "本月消费", "本月收回", "期末数", "预收款项", "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月", "应收额", "备注")
- For j = 0 To UBound(arr)
- d(arr(j)) = j + 1
- Next j
- a = 3
- With Sheets("汇总")
- .UsedRange.Offset(1).Delete
- [a2].Resize(1, UBound(arr) + 1) = arr
- For Each sht In Sheets
- If InStr(sht.Name, "汇总") = 0 Then
- arr = sht.UsedRange
- For j = 2 To UBound(arr)
- If InStr(arr(j, 1), "合计") > 0 Then
- .Cells(a, 1) = sht.Name
- For i = 1 To UBound(arr, 2)
- If VBA.IsDate(arr(2, i)) Then
- m = Month(arr(2, i)) & "月"
- Else
- m = arr(2, i)
- End If
- If d.exists(m) Then
- .Cells(a, d(m)) = arr(j, i)
- End If
- Next i
- a = a + 1
- Exit For
- End If
- Next j
- End If
- Next sht
- For j = 7 + 11 To 7 Step -1
- If .Cells(Rows.Count, j).End(3).Row = 2 Then Columns(j).Delete
-
- Next j
- End With
-
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|