When working with Excel, you may want to separate the worksheet into different several tabs (or worksheets). There are different methods to separate them.
For example, If you have only a couple of variable in the column, and this is one time work, you can use the "Move and Copy" command to have several worksheets first, then keep one variable in one worksheet. However, if you have many variables or you keep repeating this kind of work, please use the micro with the steps below:
Step 1: Pressing shortcut "Alt+F11" to open the Microsoft Visual Basic for Applications window;
Alternatively, please click the "Developer" tab from the ribbon and click "Visual Basic" to open the window.
Step 2: In the new window, click the "Insert" tab from the ribbon, and click "Module";
Step 3: Copy and paste the following codes in the Module window;
Sub parse_data() Dim lr As Long Dim ws As Worksheet Dim vcol, i As Integer Dim icol As Long Dim myarr As Variant Dim title As String Dim titlerow As Integer Application.ScreenUpdating = False vcol = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Default:="1", Type:=1) Set ws = ActiveSheet lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row title = "A1" titlerow = ws.Range(title).Cells(1).Row icol = ws.Columns.Count ws.Cells(1, icol) = "Unique" For i = 2 To lr On Error Resume Next If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol) End If Next myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants)) ws.Columns(icol).Clear For i = 2 To UBound(myarr) ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & "" If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & "" Else Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count) End If ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1") 'Sheets(myarr(i) & "").Columns.AutoFit Next ws.AutoFilterMode = False ws.Activate Application.ScreenUpdating = True End Sub
Step 4: Click the "Run Sub" button (or press the F5 key) to run the codes;
Step 5: In the pop-up box, enter the column number, e.g., 1, and the worksheet will be split based on the variables in the column;
Step 6: New tabs will appear after.
If you want to further split each tab into separate files, please refer to the post here.