|
求一段代码,实现每月盘点表快速汇总,谢谢
- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim Filename, wb As Workbook, Sht As Worksheet, ThisSh As Worksheet
- Filename = Dir(ThisWorkbook.Path & "\*.xls")
- Set ThisSh = ActiveSheet
- ThisSh.Range("a2:h65536").ClearContents
- Do While Filename <> ""
- If Filename <> ThisWorkbook.Name Then
- fn = ThisWorkbook.Path & "" & Filename
- Set wb = Workbooks.Open(fn)
- mc = wb.Worksheets(1).[a9]
- For i = 2 To 8
- If i = 5 Or i = 8 Then tj = "特价" Else tj = "正价"
- Set Sht = wb.Worksheets(i)
- r = ThisSh.UsedRange.Rows.Count + 1
- ThisSh.Cells(r, 1).Resize(34, 6).Value = Sht.[b7:g40].Value
- r = ThisSh.UsedRange.Rows.Count + 1
- ThisSh.Cells(r, 1).Resize(34, 6).Value = Sht.[k7:r40].Value
- r = ThisSh.UsedRange.Rows.Count + 1
- r1 = ThisSh.[g65536].End(3).Row + 1
- ThisSh.Cells(r1, "g").Resize(r - r1).Value = mc
- ThisSh.Cells(r1, "h").Resize(r - r1).Value = tj
- Next
- wb.Close False
- End If
- Filename = Dir
- Loop
- Set Sht = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|