Practical Excel skills sharing: two ways to quickly split a worksheet

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.

Type 1: Extremely fast splitting - VBA (code is provided in the article)

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.

Sub Split 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 column A Get the number of rows in the worksheet starting from the last row. Generally, only the column parameters in Range are changed. If you want the effective range of the worksheet to start from the B column, the value is B65535.

iCol = .Range("IV1").End(xlToLeft).Column'From the last column (IV ) Get the number of columns of the worksheet starting from row 1. Generally, only the row parameters in Range are changed. If you want the effective range of the worksheet to be from the Starting with line 2, the value is IV2

## t = 3 't is the number of columns, set which column to split based on, for example, if you press EColumn splitting, here is t=5

​​ For i = 2 To iRow 'i is 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 ​​in Range and the row values ​​in Resize are changed, such as work The title of the table starts from row 3 in column B, then this code becomes sh.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 ​​in Range are changed, for example, the worksheet is from BThe beginning of the column, here becomes Range("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 ​​of Range are changed. If the worksheet is from B If the column starts, change B to Range("B" & iNum 1).Resize(1, iCol).Value = .Range("B" & i).Resize(1, iCol).Value

         Next i

End 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.

Type 2: Conventional splitting - Pivot table

Pivot table is really easy to use, it not only has absolute advantages in data statistical analysis , and using the filter page can also help us realize the function of splitting the worksheet. The steps are as follows:

(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!