Split Data into Multiple sheets

[mohan4u]

hi
I have a data in a excel file and need to divide in multiple sheets(this would be a input box where i enter no of sheets to be added in worksheet) and data divided in these sheets.
plz help...

 

[Hui]

Mohan4u

Firstly, Welcome to the Chandoo.org Forums

Assuming you need to split the data based on some criteria

Start by reading these two posts:

Move Data to other Sheets
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/
Advanced Filter Move Data to other Files
http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

 

[mohan4u]

thanks hui for this information....

as per the examples shared by you is really helpfull..

What Exactly I am looking for is...

like suppose I have 700 rows with 10 column.

and i want to divide this 700 rows in the same workbook with prompted sheet(in numbers) like 4, 5 7 or 7 .

like if i enter 7 in inputbox then, 7 different sheets will create in the same workbook with 100 data in each sheet....

Dim ws, tr As Variant
ws = InputBox("How Mant Sheets you Would Like To Create", "Prompt")
If ws = "" Then Exit Sub
If IsNumeric(ws) Then
Sheets.Add after:=Sheet2, Count:=ws
Else
MsgBox "Invalid Number"
End If

Please help....

 

[Hui]

Can you try the following code

Sub SplitData()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim lr As Integer, lc As Integer

On Error Resume Next
Set WorkRng = Application.Selection
lr = Range("A" & Rows.Count).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
' Assumes the data starts in A1
Set WorkRng = Range(Cells(1, 1), Cells(lr, lc))
SplitRow = Application.InputBox("Split Row Num", "How many rows?", 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("A1").PasteSpecial
  Application.ActiveSheet.Name = CStr(i) + "-" + CStr(i + SplitRow - 1)
  Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[mohan4u]

Thanks Hui for the code and appreciate your help.
However, getting error or system gets unresponsive when i click cancel when input box appears..
Also i have made sone changes in the code...

...if i want to get the summary like pivot table of the outcome of this code in a sheet
Like how many data divided in each sheets with sheet name ... its kind of summary of everything