If the worksheet is summarized, it will be split. How to quickly split a worksheet? In this article, we share two methods for quickly splitting worksheets that increase efficiency by 99.99%. I hope it will be helpful to everyone!
Have you ever encountered such a problem: after we summarize all the information in a table, we need to sort this large table according to a certain The conditions are then split into multiple worksheets. How can this be achieved? Perhaps the stupidest method is to filter the data in the original worksheet and then copy and paste it to the new worksheet. However, this method is not suitable for cases with a lot of data, and the new worksheets also need to be renamed one by one, which is cumbersome. Today I will introduce to you two quick and practical methods for splitting worksheets.
As shown in the picture, now we need to split the contents of this worksheet into multiple worksheets by city.
VBA is EXCEL that handles a large amount of repetitive work The best tool to use. However, many people know nothing about VBA, so today I will share a piece of code with you, and explain in detail how to modify the code value according to the actual table, so that you can use it at work.
(1) Press Alt F11 to open the VBA editor, and click "Module" under the "Insert" menu.
(2) Enter the following code in the code window on the right. If you don’t want to input it by hand, you can join a group to download the prepared code file and just copy and paste it.
Sub split table()
Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String Application.ScreenUpdating = False With Worksheets("Sheet1") iRow = .Range("A65535").End(xlUp).Row iCol = .Range("IV1").End(xlToLeft).Column t = 3 For i = 2 To iRow str = .Cells(i, t).Value On Error Resume Next Set sh = Worksheets(str) If Err.Number <> 0 Then Set sh = Worksheets.Add(, Worksheets(Worksheets.Count)) sh.Name = str End If sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value iNum = sh.Range("A" & Rows.Count).End(xlUp).Row sh.Range("A" & iNum + 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value Next i End With Application.ScreenUpdating = True End Sub
Code analysis:
The red text here indicates the code parameters that need to be modified according to the actual situation; ' is used to indicate comments. The following text does not affect the operation of the code, but is only used to explain the code. The annotation text is specifically shown in gray here.
SubSplit table'File name, modify it according to your own file name
Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String
Application.ScreenUpdating = False'Turn off screen refresh
With Worksheets("Sheet1")'The double quotes are the workbook name,according to the actual workbook Name modification
iRow = .Range("A65535").End(xlUp).Row'from columnAGet the number of rows in the worksheet starting from the last row. Generally, only the column parameters inRangeare changed. If you want the effective range of the worksheet to start from theBcolumn, the value isB65535.
iCol = .Range("IV1").End(xlToLeft).Column'From the last column (IV) Get the number of columns of the worksheet starting from row1. Generally, only the row parameters inRangeare changed. If you want the effective range of the worksheet to be from theStarting with line 2, the value isIV2
## t =3'tis the number of columns, set which column to split based on, for example, if you pressEColumn splitting, here ist=5
For i =2To iRow'iis the number of rows. Set the row from which to get the split value. It should be based on the work. Actual changes in the table
str = .Cells(i, t).Value'Get the value of the cell (i, t) as the split table name
On Error Resume Next
Set sh = Worksheets(str)'Create a worksheet named after the above obtained value
## If Err.Number 0 Then'If this worksheet does not exist, add one and name it
Set sh = Worksheets.Add(, Worksheets(Worksheets.Count))## sh.Name = str
End If
'If this worksheet exists
sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value'Get the worksheet title. Generally, only the column values inRangeand the row values inResizeare changed, such as work The title of the table starts from row3in columnB, then this code becomessh.Range("B1").Resize(3, iCol).Value = .Range("B1").Resize(3, iCol).Value '
## iNum = sh.Range("A" & Rows.Count).End(xlUp).Row'Generally only the column values inRangeare changed, for example, the worksheet is fromBThe beginning of the column, here becomesRange("B" & Rows.Count).End(xlUp).Row
## sh.Range("A" & iNum 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value
'Paste the worksheet data in the new table. Generally, only the column values ofRangeare changed. If the worksheet is fromBIf the column starts, changeBtoRange("B" & iNum 1).Resize(1, iCol).Value = .Range("B" & i).Resize(1, iCol).Value
Next iEnd With
Application.ScreenUpdating = True
'Turn on screen refresh
End Sub(3) After the code input is completed, click "Run Subprocess" in the menu bar. In this way, the worksheet is split.
Complete as follows:
In this way, the worksheet splitting is completed with one click.
(1) Select any cell in the data source and click "Pivot Table" under the Insert tab. Select an existing worksheet and click OK.
(2) Put the field "City" to be split into the filter field, the "Date" and "Salesperson" fields into the row field, and the "Sales" field into the value field .
(3) Modify the pivot table format to facilitate the formation of a table format when generating a new worksheet.
Select "Display as Table" in the "Report Layout" drop-down menu in the "Design" tab under "PivotTable Tools".
Select "Repeat All Item Labels" in the "Report Layout" drop-down menu in the "Design" tab under "PivotTable Tools".
Select "Do not show subtotals" in the "Subtotals" drop-down menu in the "Design" tab under "PivotTable Tools".
The completed results are as follows:
(4) Finally, split the pivot table into each worksheet. Select "Show Report Filter Page" from the "Options" drop-down menu in the "Pivot Table" function block on the "Analysis" tab under "Pivot Table Tools" and select "City" as the field of the report filter page to be displayed.
# (5) In order to facilitate subsequent processing, modify the pivot table into a normal table. Select the first worksheet "Beijing", hold down Shift, and click on the last worksheet "Chongqing" to form a worksheet group. This enables unified operations on all worksheets in batches.
Select all copy and paste as value.
Delete the first two rows, then adjust the width of the date column and you are done. The results are as follows:
# Pivot table method is easier to use, but there are more steps. VBA is simple to operate, but there are many things to learn. Everyone chooses to use it according to their actual situation. If you think it is good, please like it!
Related learning recommendations:excel tutorial
The above is the detailed content of Practical Excel skills sharing: two ways to quickly split a worksheet. For more information, please follow other related articles on the PHP Chinese website!