how to separate data into sheets with X rows

Microsoft Excel 2003 (full product)
September 30, 2010 at 10:52:43
Specs: Windows XP, .
Dear all,

I am quite new into macros in Excel and I would like to ask you to help me to solve this problem.
I have a .xls file with almost 40.000 rows of data (starting from A1). I would like to separate them within the same file into different worksheets where every would contain 240 rows only. That means that in first worksheet would be first 240 rows, in the second one another 240 rows and so on until the end. If possible the original worksheet should stay untouched.

Thank you for your help and I look forward your answers.

Peter


See More: how to separate data into sheets with X rows

Report •


#1
September 30, 2010 at 11:53:12
In order to do that, you'll need 166 sheets created.

We can write VBA code to create them and then copy the data in blocks of 240, but then you'll come back and ask us how to rename the 166 tabs. ;-)

You also might want header rows on each sheet which can also be covered in the code.

Why not tell us everything you think you need done and we'll try to do it all in one shot.


Report •

#2
September 30, 2010 at 14:59:51
It is 167 sheets in fact. :)

Here is the assignment:
I have 40.000 cells in column A, no other row is filled in (no heater rows or other stuff at all). In cells there are no numbers, the text only. It would be great if this "master" worksheet could stay like it is and then some new worksheets will be created and placed next to it. Their name is not important at all (though it would be nicer if they will be named from 1 to 167 :) )

The only thing I am little bit concerned about is the fact how many worksheets can be in one workbook. I am using Excel 2003, I think it is more than 250, am I right? In that case it would be ok.

And that is it. Nothing less, nothing more.

Thank you very much !

Peter


Report •

#3
September 30, 2010 at 15:16:19
There is one thing that came up to my mind:
when creating new worksheets, it usually insert it at the beginning. Like when you have "Sheet1", "Sheet2" and you insert new one, it is put to the most left position, like this: "Sheet3", "Sheet1", "Sheet2".

To sum up, would it be possible that sheets will be in ascending order, with the "master sheet" at the beginning? (like this: MasterSheet, Sheet1, Sheet2, etc.) Then it would be more natural to work with

Many thanks for your response.

Peter


Report •

Related Solutions

#4
September 30, 2010 at 15:31:25
Right click the sheet tab for MasterSheet and chose View Code
Paste this code into the pane that opens.
Click anywhere in the code and run it.

Even though this code should do nothing to your original data, I suggest that you run it in a backup copy of your workbook since Macros cannot easily be undone.

Option Explicit

Sub SplitData()
Dim lastRow, rw, shtNum As Double
'Determine last Row with Data
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
  For rw = 1 To lastRow Step 240
'Add a sheet
    Sheets.Add After:=Worksheets(Worksheets.Count)
'Increment shtNum variable
     shtNum = shtNum + 1
      With ActiveSheet
'Name the new Sheet
       .Name = "Sheet" & shtNum
'Copy 240 cells to the new sheet
        Sheets(1).Range("A" & rw & ":A" & rw + 239).Copy _
           Destination:=.Range("A1")
      End With
  Next
End Sub


Report •

#5
September 30, 2010 at 15:46:44
Many thanks man! You have helped me a lot !!!

May I ask, I have found this code by the time. It is great that here I can also change the number of rows.. Would it be impolite if I ask you to combine this two codes? Would it be possible to add in this one ascending order and the thing you have named "Name new sheet"?

Thank you for your help, you made my day..

Peter

Sub CopyTable()

'Set dimensions
Dim Table As Range, TableArray(), _
CutValue As Integer, Cntr As Integer, _
TempArray(), Width As Integer, _
x As Integer, y As Integer, _
Height As Long, Rep As Integer, _
LoopReps As Long

'Get data
Set Table = Application.InputBox("Specify range to copy", _
Default:=ActiveCell.CurrentRegion.Address, Type:=8)
CutValue = InputBox("How many rows should the chunks be?", _
Default:=900)
Width = Table.Columns.Count
Height = Table.Rows.Count

'Write to array
TableArray = Table
ReDim TempArray(1 To CutValue, 1 To Width)
Rep = Application.WorksheetFunction.RoundUp(Height / CutValue, 0)
LoopReps = CutValue

'Loop through all new sheets
For Cntr = 0 To Rep - 1
If Height - Cntr * CutValue < CutValue Then _
LoopReps = Height - Cntr * CutValue

For x = 1 To Width
For y = 1 To LoopReps
TempArray(y, x) = TableArray(y + Cntr * CutValue, x)
Next y
Next x

Worksheets.Add
Range("A1").Resize(LoopReps, Width) = TempArray
Next Cntr
End Sub


Report •

#6
September 30, 2010 at 20:38:58
Remember what I said in my first response?

Why not tell us everything you think you need done and we'll try to do it all in one shot.

If all you want to do is be able to specify the number of rows to copy, you don't all of the "stuff" that the code you found does.

In this version of my code, I no longer hard-code the 240 into the macro. Instead I use a variable and ask the user to supply the value for that variable.

Option Explicit

Sub SplitDataUserInput()
Dim lastRow, rw, numRows, shtNum As Double
'Get the number of rows to copy
 numRows = Application.InputBox("How many rows should the chunks be?", _
  Type:=1, Default:=240)
'Determine last Row with Data
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
  For rw = 1 To lastRow Step numRows
'Add a sheet
    Sheets.Add After:=Worksheets(Worksheets.Count)
'Increment shtNum variable
     shtNum = shtNum + 1
      With ActiveSheet
'Name the new Sheet
       .Name = "Sheet" & shtNum
'Copy rows
        Sheets(1).Range("A" & rw & ":A" & rw + numRows - 1).Copy _
         Destination:=.Range("A1")
      End With
  Next
End Sub


Report •

#7
October 1, 2010 at 12:53:29
Hello,

you were right when saying it is better to do everything in one shot, but at that time I didn't really know what I want. Once again thank you for your help.

During the time I have saved when using this macro I will be learning macros :)

Peter


Report •


Ask Question