Macro that splits an Excel sheet into 300 rows per sheet

A macro that divides an excel sheet into one sheet every 300 rows!

Public Sub mySub()

Dim shS As Worksheet: Set shS = ActiveSheet 'Source data sheet, current active sheet

Dim rS&: rS = 1 'Source data table, start reading data from this row

Dim rC&: rC = 300 'The number of rows read each time

Dim rNew$: rNew = 1 'Create a new table and paste the data into this row

Dim rZ&: rZ = shS.UsedRange.Row shS.UsedRange.Rows.Count - 1

Dim shNew As Worksheet, nm$, n%, r&

r = rS

Do While r

n = n 1

Set shNew = Worksheets.Add(after:=Sheets(Worksheets.Count))

nm = "Table" & rC & "_＂" & n

Call ShNm(shNew, nm)

shS.Rows(r).Resize(rC).Copy shNew.Rows(rNew)

r = rC * n rS

Loop

MsgBox "ok"

End Sub

Public Sub ShNm(sh As Worksheet, nm As Variant)

On Error Resume Next

100:

sh.Name = nm

If Err.Number 0 Then

Err.Clear

nm = Application.InputBox( _

" " " & nm & " " already exists! " & Chr(10) & Chr(10) & "Please enter a new table name: ", _

"Please enter the new table name", nm & "_new", _

Type:=2)

If nm = False Then MsgBox "The input is incorrect, exit the program!": End

GoTo 100

End If

End Sub

How to use macro commands to split a sequence in EXCEL, for example, split PL10 120 into

Sub Macro6()

'

' Macro6 Macro

'

'

Selection.TextToColumns Destination:=Range(＂A1＂), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

:=＂*＂, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Columns(＂A:A＂).Select

Selection.Replace What:=＂PL＂, Replacement:=＂＂, LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Columns(＂C:D＂).Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range(＂C1＂).Select

ActiveCell.FormulaR1C1 = "=MIN(RC[-2],)"

Range(＂C1＂).Select

ActiveCell.FormulaR1C1 = "=MIN(RC[-2],RC[-1])"

Range(＂D1＂).Select

ActiveCell.FormulaR1C1 = "=MAX(RC[-3],RC[-2])"

Range(＂C1:D1＂).Select

Selection.AutoFill Destination:=Range("C1:D1000")

Range(＂C:D＂).Select

Columns(＂A:B＂).Select

Range(＂B1＂).Activate

Columns(＂C:D＂).Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Columns(＂A:B＂).Select

Range(＂B1＂).Activate

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Columns(＂A:B＂).Select

Selection.Replace What:=＂0＂, Replacement:=＂＂, LookAt:=xlWhole, _

_

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

End Sub

Note: When using it, select column A first and then run the macro. The column to be split must be in column A, and the two columns BC are empty, otherwise it will be overwritten (haha, the time is short, not particularly smart) and the number of rows No more than 1000 lines. Haha, otherwise it will be a bit slow, so the range is set at 1000 lines. Are you also engaged in steel structures? Haha, too

How to use macros in EXCEL to split the automatic symbols with A and these in the table into the following columns

Option Explicit

Sub test()

Dim rng As Range

Dim arr As Variant

Dim k As Integer

For Each rng In Selection

rng.Value = Replace(rng.Value, ＂:＂, ＂/＂)

arr = Split(rng.Value, ＂/＂)

k = UBound(arr) 1

rng.Resize(1, k) = arr

Erase arr

Next rng

End Sub I think you know how to paste the code, so I won’t go into details. Just press the image below to run the code I wrote for you:

step-1

step-2

step-3

step-4

How to quickly split an excel table into multiple excel tables and retain the original formulas

Click [Development Tools]-[Visual Basic] or the Alt F11 shortcut key to enter the VBE editing interface.

Choose to insert a new module

Paste the following code into the module:

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 title 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, such as: "Name"", Type:=8)

title = titleRange.Value

The above is the detailed content of Macro that splits an Excel sheet into 300 rows per sheet. For more information, please follow other related articles on the PHP Chinese website!