| 楼主 yy900824 |
合并工作表，同时添加两列，并将客户编码和授权编码填入 以下是我用的宏，百度来的~~ 合并工作薄 Sub MergeWorkbooks() Dim FileSet Dim i As Integer On Error GoTo 0 Application.ScreenUpdating = False FileSet = Application.GetOpenFilename(FileFilter:="Excel 2003(*.xls),*.xls,Excel 2007(*.xlsx),*.xlsx", _ MultiSelect:=True, Title:="选择要合并的文件") If TypeName(FileSet) = "Boolean" Then GoTo ExitSub End If For Each Filename In FileSet Workbooks.Open Filename Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Next ExitSub: Application.ScreenUpdating = True End Sub 合并Sheet Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(what:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub MergeSheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long Application.ScreenUpdating = False Application.EnableEvents = False '新建一个“汇总”工作表 Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("汇总").Delete On Error GoTo 0 Application.DisplayAlerts = True Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "汇总" '开始复制的行号，忽略表头，无表头请设置成1 StartRow = 2 For Each sh In ActiveWorkbook.Worksheets If sh.Name <> DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh) If shLast > 0 And shLast >= StartRow Then Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)) If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "内容太多放不下啦！" GoTo ExitSub End If CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitSub: Application.GoTo DestSh.Cells(1) DestSh.Columns.AutoFit Application.ScreenUpdating = True Application.EnableEvents = True End Sub QQ截图20131127203735.jpg   Desktop.zip |
| 2楼 0Mouse |
看看这个帖子的13楼是否有帮助 |
| 3楼 guang0001 |
感想分享。 |
| 4楼 yy900824 |
合并工作表，同时添加两列，并将客户编码和授权编码填入 以下是我用的宏，百度来的~~ 合并工作薄 Sub MergeWorkbooks() Dim FileSet Dim i As Integer On Error GoTo 0 Application.ScreenUpdating = False FileSet = Application.GetOpenFilename(FileFilter:="Excel 2003(*.xls),*.xls,Excel 2007(*.xlsx),*.xlsx", _ MultiSelect:=True, Title:="选择要合并的文件") If TypeName(FileSet) = "Boolean" Then GoTo ExitSub End If For Each Filename In FileSet Workbooks.Open Filename Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Next ExitSub: Application.ScreenUpdating = True End Sub 合并Sheet Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(what:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub MergeSheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long Application.ScreenUpdating = False Application.EnableEvents = False '新建一个“汇总”工作表 Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("汇总").Delete On Error GoTo 0 Application.DisplayAlerts = True Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "汇总" '开始复制的行号，忽略表头，无表头请设置成1 StartRow = 2 For Each sh In ActiveWorkbook.Worksheets If sh.Name <> DestSh.Name Then Last = LastRow(DestSh) shLast = LastRow(sh) If shLast > 0 And shLast >= StartRow Then Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)) If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "内容太多放不下啦！" GoTo ExitSub End If CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitSub: Application.GoTo DestSh.Cells(1) DestSh.Columns.AutoFit Application.ScreenUpdating = True Application.EnableEvents = True End Sub QQ截图20131127203735.jpg Desktop.zip |
| 5楼 guang0001 |
感想分享。 |
| 6楼 0Mouse |
看看这个帖子的13楼是否有帮助 |