建个网站怎么做推广,深圳住房和建设部网站,wordpress 文章视频,html5国内网站建设Excel拆分 工作表按行拆分为工作薄 工作表按行拆分为工作薄
打开要拆分的Excel文件#xff0c;使用快捷键#xff08;AltF11#xff09;打开脚本界面#xff0c;选择要拆分的sheet#xff0c;打开Module#xff0c;在Module中输入脚本代码#xff0c;然后运行脚本
Su… Excel拆分 工作表按行拆分为工作薄 工作表按行拆分为工作薄
打开要拆分的Excel文件使用快捷键AltF11打开脚本界面选择要拆分的sheet打开Module在Module中输入脚本代码然后运行脚本
Sub 工作表按行拆分为工作薄()Dim tm As DateDim fso As ObjectDim ws As WorksheetDim wb As WorkbookDim newWb As WorkbookDim savePath As StringDim wbPath As StringDim wbName As StringDim saveFile As StringDim titleRow As LongDim numRows As LongDim maxRow As LongDim sheetCount As LongDim i As LongDim lastRowCopied As Long 初始化tm NowApplication.Visible FalseApplication.DisplayAlerts FalseSet fso CreateObject(Scripting.FileSystemObject) 参数设置titleRow 1numRows 50000Set ws ThisWorkbook.ActiveSheetwbPath ThisWorkbook.PathwbName ThisWorkbook.NamesavePath wbPath \split 创建保存路径文件夹如果不存在If Not fso.FolderExists(savePath) Thenfso.CreateFolder savePathEnd If 计算最大行数和拆分后的工作表数量maxRow ws.UsedRange.Rows.CountsheetCount WorksheetFunction.RoundUp((maxRow - titleRow) / numRows, 0) 循环拆分并保存工作簿On Error GoTo ErrorHandlerFor i 1 To sheetCount 创建新工作簿Set newWb Workbooks.AddWith newWb.Sheets(1) 复制表头ws.Rows(1: titleRow).Copy Destination:.Rows(1: titleRow) 复制数据lastRowCopied numRows * (i - 1) titleRow numRowsIf lastRowCopied maxRow Then lastRowCopied maxRowws.Rows(numRows * (i - 1) titleRow 1 : lastRowCopied).Copy Destination:.Rows(titleRow 1) 复制列宽可选.Columns(A:Z).AutoFit 或者指定需要的列End With 保存新工作簿saveFile savePath \ fso.GetBaseName(wbName) _split i . fso.GetExtensionName(wbName)newWb.SaveAs Filename:saveFilenewWb.Close FalseSet newWb Nothing 释放新工作簿对象Next i 清理和恢复设置Set fso NothingApplication.Visible TrueApplication.DisplayAlerts TrueDebug.Print 工作表已拆分完成累计用时 Format(Now() - tm, hh:mm:ss)Exit SubErrorHandler:MsgBox 错误 Err.Number : Err.Description, vbCritical 清理和恢复设置错误处理中的清理If Not newWb Is Nothing Then newWb.Close FalseSet fso NothingApplication.Visible TrueApplication.DisplayAlerts True
End Sub
