Sub 计算()
'
' 计算 宏
'
'关闭屏幕更新和计算
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'寻找今日销售业绩数据
sales = Dir("D:\众结资料\1日常工作内容\每日销售开发业绩(Python)\" & Application.WorksheetFunction.Text(Date, "yyyymmdd") & "\销售业绩本月.csv")
If sales = "" Then
MsgBox "未找到今天的销售业绩数据"
Exit Sub
Else
End If
'寻找上月销售业绩数据
saleslast = Dir("D:\众结资料\1日常工作内容\每日销售开发业绩(Python)\" & Application.WorksheetFunction.Text(Date, "yyyymmdd") & "\销售业绩上月.csv")
If saleslast = "" Then
MsgBox "未找到上月的销售业绩数据"
Exit Sub
Else
End If
'确定销售页有多少条数据
HROW1 = Sheets("销售业绩").[B2].CurrentRegion.Rows.Count
HROW2 = Sheets("销售业绩").[N2].CurrentRegion.Rows.Count
HROW3 = Sheets("各仓库销售业绩").[C2].CurrentRegion.Rows.Count
HROW4 = Sheets("开发业绩").[C2].CurrentRegion.Rows.Count
HROW5 = Sheets("开发业绩").[O2].CurrentRegion.Rows.Count
HROW6 = Sheets("店铺业绩").[A2].CurrentRegion.Rows.Count
HROW7 = Sheets("站点数据").[A2].CurrentRegion.Rows.Count
HROW8 = Sheets("店铺业绩ebay").[A2].CurrentRegion.Rows.Count
'删除销售数据
Sheets("销售业绩").Range("A2:I" & HROW1).Clear
Sheets("销售业绩").Range("M2:O" & HROW2).Clear
Sheets("各仓库销售业绩").Range("A2:D" & HROW3).Clear
Sheets("开发业绩").Range("A2:D" & HROW4).Clear
Sheets("开发业绩").Range("M2:O" & HROW5).Clear
Sheets("店铺业绩").Range("A2:E" & HROW6).Clear
Sheets("站点数据").Range("A2:E" & HROW7).Clear
Sheets("店铺业绩ebay").Range("A2:F" & HROW8).Clear
'复制本期销售业绩
Set saleswb = Workbooks.Open("D:\众结资料\1日常工作内容\每日销售开发业绩(Python)\" & Application.WorksheetFunction.Text(Date, "yyyymmdd") & "\" & sales)
HROWsl = saleswb.Sheets("销售业绩本月").[B2].CurrentRegion.Rows.Count
saleswb.Sheets("销售业绩本月").Range("A2:I" & HROWsl).Copy ThisWorkbook.Worksheets("销售业绩").Range("A2")
saleswb.Close SaveChanges:=False
'复制上期销售业绩
Set saleslastwb = Workbooks.Open("D:\众结资料\1日常工作内容\每日销售开发业绩(Python)\" & Application.WorksheetFunction.Text(Date, "yyyymmdd") & "\" & saleslast)
HROWslast = saleslastwb.Sheets("销售业绩上月").[B2].CurrentRegion.Rows.Count
saleslastwb.Sheets("销售业绩上月").Range("A2:C" & HROWslast).Copy ThisWorkbook.Worksheets("销售业绩").Range("M2")
saleslastwb.Close SaveChanges:=False
'打开计算
Application.Calculation = xlCalculationAutomatic
'关闭计算
Application.Calculation = xlCalculationManual
Call 业绩计算
'
End Sub
VB找到需要Excel文件,复制到需要的Excel文件的sheet中用于计算。
最新推荐文章于 2021-07-22 14:34:38 发布