|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
编写一个需要对文件夹下所有文件进行新建一个temp的工作表(已经建立的就跳过),然后在新建的temp上筛选出sheet2的上海aaa公司的对手公司及多笔汇总金额,代码基本也是东拼西凑的,可以在所有文件上新建temp,但是A列B列就是写不进去,求大神帮忙看下。
- Sub 整理()
- Dim mypath$, wj$, arr, wb As Workbook
- Dim rng as range
- dim n
- mypath = ThisWorkbook.Path & "\"
- wj = Dir(mypath & wj)
- Do While wj <> ""
- If wj <> ThisWorkbook.Name Then
- Set wb = GetObject(mypath & wj)
- For i = 1 To wb.Sheets.Count
- On Error Resume Next
- If wb.Sheets("temp") Is Nothing Then
- With wb.Sheets.Add(after:=Sheets(Sheets.Count))
- .Name = "temp"
- .Range("A1") = "户名"
- .Range("B1") = "金额"
- For Each rng In Sheet2.[B:B]
- If rng.Value = "上海AAA公司" Then
- n = n + 1
- wb.Sheets("temp").Range("a" & n + 1) = rng.Offset(0, 1).Value
- wb.Sheets("temp").Range("b" & n + 1) = WorksheetFunction.SumIf(Sheet2.Range("c:c"), Range("a" & n + 1), Sheet2.Range("F:F"))
- End If
- Next
- .Range("$A:$B").RemoveDuplicates Columns:=1, Header:=xlNo
- End With
- End If
- Next
- Application.Windows(wb.Name).Visible = True
- wb.Close 1
- End If
- wj = Dir
- Loop
- End If
- End Sub
复制代码 就是这两句总是不对。。求助
- wb.Sheets("temp").Range("a" & n + 1) = rng.Offset(0, 1).Value
- wb.Sheets("temp").Range("b" & n + 1) = WorksheetFunction.SumIf(Sheet2.Range("c:c"), Range("a" & n + 1), Sheet2.Range("F:F"))
复制代码
|
|