How excle tables split data into impassable sheet pages

This article introduces how the excle table splits the data into impassable sheet pages, the content is very detailed, interested friends can refer to it, I hope it can be helpful to you.

13 first show the final effect:

Example of starting steps:

The code is as follows

Sub CFGZB () Dim myRange As Variant Dim myArray Dim titleRange As Range Dim title As String Dim columnNum As Integer myRange = Application.InputBox (prompt:= "Please select the header row:", Type:=8) myArray = WorksheetFunction.Transpose (myRange) Set titleRange = Application.InputBox (prompt:= "Please select the split header, which must be the first row and be a cell For example, "name", Type:=8) title = titleRange.Value columnNum = titleRange.Column Application.ScreenUpdating = False Application.DisplayAlerts = False Dim iTunes, Myr&, Arr, num& Dim d K For i = Sheets.Count To 1 Step-1 If Sheets (I). Name "Sheet1" Then Sheets (I). Delete End If Next i Set d = CreateObject ("Scripting.Dictionary") Myr = Worksheets ("Sheet1"). UsedRange.Rows.Count Arr = Worksheets ("Sheet1") .range (Cells (2, columnNum), Cells (Myr, columnNum)) For I = 1 To UBound (Arr) d (Arr (I) 1)) = "" Next k = d.keys For I = 0 To UBound (k) Set conn = CreateObject ("adodb.connection") conn.Open "provider=microsoft.jet.oledb.4.0 Extended properties=excel 8.0 Data source= "& ThisWorkbook.FullName Sql =" select * from [Sheet1 $] where "& title &" ='"& k (I) &"'"Worksheets.Add after:=Sheets (Sheets.Count) With ActiveSheet. Name = k (I) For num = 1 To UBound (myArray) .cells (1, num) = myArray (num) 1) Next num .range ("A2"). CopyFromRecordset conn.Execute (Sql) End With Sheets (1). Select Sheets (1). Cells.Select Selection.Copy Worksheets (Sheets.Count). Activate ActiveSheet.Cells.Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False Transpose:=False Application.CutCopyMode = False Next i conn.Close Set conn = Nothing Application.DisplayAlerts = True Application.ScreenUpdating = TrueEnd Sub123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107

6.

7.

8.

9.

ten

11.

12 Note:

1) the original data table should have data from the first row and cannot have merged cells

2) you need to open the macro when opening the workbook, otherwise you will not be able to run the code.

2. Generate the directory

A. Create a new sheet with the name changed to "directory" in the development tool

Sub createmenu ()

For I = 1 To Sheets.Count

Cells (I, 1) = Sheets (I) .Name

'Cells (I, 2) = Sheets (I) .cells (2,2). Value

Next i

End Sub

Double-click the sheet in the directory below to paste and execute

B. Add hyperlinks

Define the hyperlink function in cell B2 in sheet1 and enter = HYPERLINK ("#" & A2 & "! A1", A2)

C. Increase the return directory

Select all sheet (except directories) and select a blank cell input = HYPERLINK ("# directory! A1", "return directory") in any one.

On the excle table how to split the data into impassable sheet pages to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.