how to split rows and paste data in to seperate sheets.

[mithelesh]

Hi all,

I have a code to split the data in to seperate sheets and paste in it.

follow as:
clik on split button.
select the data range.
and then mention for eg: 400


but can someone explain me the code.
what is inputbox do
what is workrng/parent
what does workrng.rows(1) do



Sub SplitData()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "User Defined Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
resizeCount = SplitRow
If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A2").PasteSpecial
Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


also please find the attachment.

 

[Marc L]

Hi,

in your VBA code, place text cursor on a statement, hit F1 key and read !
​

 

[mithelesh]

thab

Hi,

in your VBA code, place text cursor on a statement, hit F1 key and read !
​

thankyou Marc, i learned one better way to deal situation.

But i am finding difficul

Hi,

in your VBA code, place text cursor on a statement, hit F1 key and read !
​

thanks Marc, but finding difficult to understand the onset error , parent properties.

 

[mithelesh]

any one can help me to understand better on onset error and parent property

and also how the above macro excutes