Sub 月报表数据库统计()
Dim workDir, operWbFileName, operWbName, monthWbName
Dim operWb As Workbook
Dim sheetIdx As Integer, total As Integer, dotIdx As Integer, rowIdx As Integer, dayIdx As Integer
Dim isAppendStatSheet As String '是否将统计的Sheet追加到当前workbook的Sheet中
Dim day1Date As Date
Dim day1 As String, day2 As String
Dim dayRowNum As Integer
Application.ScreenUpdating = False
workDir = ActiveWorkbook.Path
operWbFileName = Dir(workDir & "\" & "*.xls")
monthWbName = ActiveWorkbook.Name
total = 0
sheetIdx = 8
isAppendStatSheet = MsgBox("是否将每日营业日收入报表的Sheet合并到当前的月报表workbook中？\n如果点击【是】按钮，程序将合并每日的营业日报表Sheet到当前的Workbook中\n点击【否】按钮，将不会合并，只会对数据进行统计", vbOKCancel, "确认消息")
'将多个Excel工作薄的第一个Sheet内容合并到一个工作薄
Do While operWbFileName <> ""
'将最终合并的工作薄排除在外
If operWbFileName <> monthWbName Then
Set operWb = Workbooks.Open(workDir & "\" & operWbFileName)
total = total + 1
'如果是追加模式，则将每日的营业日收入报表Sheet合并到月报表中
If isAppendStatSheet = vbOK Then
operWbName = operWbFileName
dotIdx = InStr(operWbFileName, ".")
If dotIdx > 0 Then
operWbName = Left(operWbFileName, dotIdx)
End If
'如果sheet页已经用完，则新创建一个sheet页
If sheetIdx > Workbooks(monthWbName).workSheets.Count Then
Workbooks(monthWbName).workSheets.Add(After:=Workbooks(monthWbName).workSheets(sheetIdx - 1)).Name = operWbName
Else
Workbooks(monthWbName).workSheets(sheetIdx).Name = operWbName
End If
With Workbooks(monthWbName).workSheets(sheetIdx)
operWb.Sheets(1).UsedRange.Copy .Cells(1, 1)
End With
sheetIdx = sheetIdx + 1
End If
'开始统计数据
With operWb.workSheets(1)
'获取每日营业日收入报表的核算日期（位于F2单元格内）
day1Date = .Cells(2, 6).Value
day1 = Day(day1Date)
'统计月收入汇总（先找到月收入汇总中对应的日期所在的行）
For rowIdx = 1 To Workbooks(monthWbName).workSheets("月收入汇总").Range("a65536").End(xlUp).Row
day2 = Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 1).Value
dayIdx = InStr(day2, "日")
If dayIdx > 0 Then
day2 = Left(day2, dayIdx - 1)
End If
If day1 = day2 Then
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 3).Value = .Cells(5, 3).Value '夜房费
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 4).Value = .Cells(6, 3).Value '钟点房费
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 5).Value = .Cells(7, 3).Value '商品费
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 6).Value = .Cells(8, 3).Value '客房用品
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 7).Value = .Cells(9, 3).Value '损物赔偿
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 8).Value = .Cells(10, 3).Value '杂费
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 9).Value = .Cells(11, 3).Value '会议室
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 10).Value = .Cells(32, 4).Value '株洲雅逸餐厅
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 12).Value = .Cells(5, 7).Value '现金
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 13).Value = .Cells(6, 7).Value '信用卡银联
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 14).Value = .Cells(7, 7).Value '银行汇款
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 15).Value = .Cells(8, 7).Value '储值卡
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 16).Value = .Cells(9, 7).Value '内部签单
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 17).Value = .Cells(10, 7).Value '电子券
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 18).Value = .Cells(11, 7).Value '应收挂账
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 19).Value = .Cells(12, 7).Value '个人挂账
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 20).Value = .Cells(14, 7).Value '赠送
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 21).Value = .Cells(15, 7).Value '内部招待
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 22).Value = .Cells(16, 7).Value '宾客账
Workbooks(monthWbName).workSheets("月收入汇总").Cells(rowIdx, 23).Value = .Cells(17, 7).Value '其他
Exit For
End If
Next
'统计房型数据库（先找到房型数据库中对应的日期所在的行）
For rowIdx = 1 To Workbooks(monthWbName).workSheets("房型数据库").Range("a65536").End(xlUp).Row
day2 = Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 1).Value
dayIdx = InStr(day2, "日")
If dayIdx > 0 Then
day2 = Left(day2, dayIdx - 1)
End If
If day1 = day2 Then
'统计有效出租房间数
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 5).Value = .Cells(39, 2).Value '高级大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 6).Value = .Cells(40, 2).Value '豪华大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 7).Value = .Cells(41, 2).Value '商务大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 8).Value = .Cells(42, 2).Value '商务双床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 9).Value = .Cells(43, 2).Value '豪华双床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 10).Value = .Cells(44, 2).Value '家庭双床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 11).Value = .Cells(45, 2).Value '行政大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 12).Value = .Cells(46, 2).Value '浪漫圆床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 13).Value = .Cells(47, 2).Value '休闲娱乐房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 14).Value = .Cells(48, 2).Value '商务套房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 15).Value = .Cells(49, 2).Value '豪华套房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 16).Value = .Cells(50, 2).Value '行政套房
'统计出租金额
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 18).Value = .Cells(39, 3).Value '高级大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 19).Value = .Cells(40, 3).Value '豪华大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 20).Value = .Cells(41, 3).Value '商务大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 21).Value = .Cells(42, 3).Value '商务双床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 22).Value = .Cells(43, 3).Value '豪华双床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 23).Value = .Cells(44, 3).Value '家庭双床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 24).Value = .Cells(45, 3).Value '行政大床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 25).Value = .Cells(46, 3).Value '浪漫圆床房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 26).Value = .Cells(47, 3).Value '休闲娱乐房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 27).Value = .Cells(48, 3).Value '商务套房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 28).Value = .Cells(49, 3).Value '豪华套房
Workbooks(monthWbName).workSheets("房型数据库").Cells(rowIdx, 29).Value = .Cells(50, 3).Value '行政套房
Exit For
End If
Next
'统计客源数据库（先找到客源数据库中对应的日期所在的行）
For rowIdx = 1 To Workbooks(monthWbName).workSheets("客源数据库").Range("a65536").End(xlUp).Row
day2 = Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 1).Value
dayIdx = InStr(day2, "日")
If dayIdx > 0 Then
day2 = Left(day2, dayIdx - 1)
End If
If day1 = day2 Then
'统计有效出租房间数
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 5).Value = .Cells(39, 9).Value '散客房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 6).Value = .Cells(40, 9).Value '钟点房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 7).Value = .Cells(41, 9).Value '团队房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 8).Value = .Cells(42, 9).Value '协议房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 9).Value = .Cells(43, 9).Value '会员房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 10).Value = .Cells(44, 9).Value '会议房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 11).Value = .Cells(45, 9).Value '招待房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 12).Value = .Cells(46, 9).Value '网络房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 13).Value = .Cells(47, 9).Value '午夜房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 14).Value = .Cells(48, 9).Value '半天房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 15).Value = .Cells(49, 9).Value '无客房
'统计出租金额
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 17).Value = .Cells(39, 10).Value '散客房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 18).Value = .Cells(40, 10).Value '钟点房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 19).Value = .Cells(41, 10).Value '团队房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 20).Value = .Cells(42, 10).Value '协议房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 21).Value = .Cells(43, 10).Value '会员房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 22).Value = .Cells(44, 10).Value '会议房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 23).Value = .Cells(45, 10).Value '招待房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 24).Value = .Cells(46, 10).Value '网络房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 25).Value = .Cells(47, 10).Value '午夜房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 26).Value = .Cells(48, 10).Value '半天房
Workbooks(monthWbName).workSheets("客源数据库").Cells(rowIdx, 27).Value = .Cells(49, 10).Value '无客房
Exit For
End If
Next
'统计餐厅数据库（先找到餐厅数据库中对应的日期所在的行）
For rowIdx = 1 To Workbooks(monthWbName).workSheets("餐厅数据库").Range("a65536").End(xlUp).Row
day2 = Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 1).Value
dayIdx = InStr(day2, "日")
If dayIdx > 0 Then
day2 = Left(day2, dayIdx - 1)
End If
If day1 = day2 Then
'统计数量
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 3).Value = .Cells(21, 3).Value '点菜系列
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 4).Value = .Cells(22, 3).Value '套餐系列
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 5).Value = .Cells(23, 3).Value '休闲系列
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 6).Value = .Cells(24, 3).Value '小吃、面点
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 7).Value = .Cells(25, 3).Value '自助早餐
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 8).Value = .Cells(26, 3).Value '茗茶
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 9).Value = .Cells(27, 3).Value '花茶
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 10).Value = .Cells(28, 3).Value '果汁
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 11).Value = .Cells(29, 3).Value '咖啡
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 12).Value = .Cells(30, 3).Value '酒水
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 13).Value = .Cells(31, 3).Value '其他
'统计金额
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 15).Value = .Cells(21, 4).Value '点菜系列
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 16).Value = .Cells(22, 4).Value '套餐系列
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 17).Value = .Cells(23, 4).Value '休闲系列
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 18).Value = .Cells(24, 4).Value '小吃、面点
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 19).Value = .Cells(25, 4).Value '自助早餐
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 20).Value = .Cells(26, 4).Value '茗茶
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 21).Value = .Cells(27, 4).Value '花茶
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 22).Value = .Cells(28, 4).Value '果汁
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 23).Value = .Cells(29, 4).Value '咖啡
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 24).Value = .Cells(30, 4).Value '酒水
Workbooks(monthWbName).workSheets("餐厅数据库").Cells(rowIdx, 25).Value = .Cells(31, 4).Value '其他
Exit For
End If
Next
End With
'关闭打开的每日营业收入报表
operWb.Close False
End If
operWbFileName = Dir
Loop
Application.ScreenUpdating = True
'MsgBox "共合并了" & total & "个工作薄下的工作表。", "提示"
End Sub本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
