VBS realizes that the worksheet is automatically divided into tables according to the specified header

Time:2022-5-10

In our actual work, we often encounter the situation of separating the worksheet according to a certain header field. Our general practice is to sort by the specified header first, and then copy and paste it in sections. It is not only troublesome but also easy to make mistakes.

The following vbs script is to realize the function of automatically dividing the worksheet according to the specified header (selected by the user). If you need a friend, just drag and drop the worksheet to be operated to the script file to easily realize worksheet splitting (temporarily only applicable to XP system):

Copy codeThe code is as follows:


‘drag the worksheet to the vbs script to automatically divide the table according to the specified header
On Error Resume Next
If WScript.Arguments(0) = “” Then WScript.Quit
Dim objExcel, ExcelFile, MaxRows, MaxColumns, SHCount
ExcelFile = WScript.Arguments(0)
If LCase(Right(ExcelFile,4)) <> “.xls” And LCase(Right(ExcelFile,4)) <> “.xls” Then WScript.Quit
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = False
objExcel.Workbooks.Open ExcelFile
‘get the total number of initial sheets of the worksheet
SHCount = objExcel.Sheets.Count
‘get the number of valid rows and columns of the worksheet
MaxRows = objExcel.ActiveSheet.UsedRange.Rows.Count
MaxColumns = objExcel.ActiveSheet.UsedRange.Columns.Count
‘get the header list of the first row of the worksheet
Dim StrGroup
For i = 1 To MaxColumns
StrGroup = StrGroup & “[” & i & “]” & vbTab & objExcel.Cells(1, i).Value & vbCrLf
Next
‘user specified sub table header and input validity judgment
Dim Num, HardValue
Num = InputBox (“please input the serial number of sub table header” & vbcrlf & strgroup)
If Num <> “” Then
Num = Int(Num)
If Num > 0 And Num <= MaxColumns Then
HardValue = objExcel.Cells(1, Num).Value
Else
objExcel.Quit
Set objExcel = Nothing
WScript.Quit
End If
Else
objExcel.Quit
Set objExcel = Nothing
WScript.Quit
End If
‘get the header value and number of sub tables
Dim ValueGroup : j = 0
Dim a() : ReDim a(10000)
For i = 2 To MaxRows
str = objExcel.Cells(i, Num).Value
If InStr(ValueGroup, str) = 0 Then
a(j) = str
ValueGroup = ValueGroup & str & “,”
j = j + 1
End If
Next
ReDim Preserve a(j-1)
‘create a new sheet and name it with the specified header value
For i = 0 To UBound(a)
If i + 2 > SHCount Then objExcel.Sheets.Add ,objExcel.Sheets(“sheet” & i + 1),1,-4167
Next
For i = 0 To UBound(a)
objExcel.Sheets(“sheet” & i + 2).Name = HardValue & “_” & a(i)
Next
‘write data in separate table
For i = 1 To MaxRows
For j = 1 To MaxColumns
objExcel.sheets(1).Select
str = objExcel.Cells(i,j).Value
If i = 1 Then
For k = 0 To UBound(a)
objExcel.sheets(HardValue & “_” & a(k)).Select
objExcel.Cells(i,j).Value = str
objExcel.Cells(1, MaxColumns + 1).Value = 1
Next
Else
objExcel.sheets(HardValue & “_” & objExcel.Cells(i,Num).Value).Select
If j = 1 Then x = objExcel.Cells(1, MaxColumns + 1).Value + 1
objExcel.Cells(x ,j).Value = str
If j = MaxColumns Then objExcel.Cells(1, MaxColumns + 1).Value = x
End If
Next
Next
For i = 0 To UBound(a)
objExcel.sheets(HardValue & “_” & a(i)).Select
objExcel.Cells(1, MaxColumns + 1).Value = “”
Next
objExcel.ActiveWorkbook.Save
objExcel.Quit
Set objExcel = Nothing
WScript. Echo “prompt: the table splitting operation for” & excelfile & “is completed”