VBA Macro required

[philla]

i have a workbook with 2 worksheets Mother and child, i need the mother worksheet to be split into rows of 50 and pasted into new worksheet named "data1" and the next 50 rows of data to new worksheet "data2" till all the data is split ( the data is in coloums A2 to K2 and the rows can be any size i.e 140 so the new worksheets will be data1 (50 rows) data 2 (50 rows) and data 3 (39 rows)

Thanks

 

[Luke M]

How's this? Assumes that you start the macro with the Mother worksheet currently selected.

[pre]

Sub SplitData()
Dim MyData As Range
Dim LastRow As Integer
Dim i As Integer
Dim k As Integer

'How many rows of data are there?
LastRow = Range("A65536").End(xlUp).Row

'Now we can define the range of data
Set MyData = Range("A2", Cells(LastRow, "K"))

i = 1
k = 1

Application.ScreenUpdating = False
With MyData
Do While i + 1 < LastRow
'Will put the new sheets after "Mother" sheet
ThisWorkbook.Worksheets.Add after:=ActiveSheet
'Give the new sheet a name
ActiveSheet.Name = "data" & k
'Copy the data
.Range(.Cells(i, "A"), .Cells(i + 49, "K")).Copy ActiveSheet.Range("A1")
i = i + 50
k = k + 1
Loop
End With

Application.ScreenUpdating = True
End Sub

[/pre]

 

[Montrey]

Luke,

I will have your babies. Just sayin

 

[philla]

Luke that’s great works a treat!! Just one more big thing where the Mother data is split into 50 rows I need the last row finding in the child data and the child data needs to be split into rows of 500 and put into child tabs.


Mother Data example.

Number Site Date

XXX West Brom 19/08/2012 (This would be the data on row 50 so we need to search for site in column” b” and date in” c” on the Child Data


Child data example

Site Number Date

West Brom xxxxx 18/08/2012

West Brom xxxxx 18/08/2012

West Brom xxxxx 19/08/2012

West Brom xxxxxx 19/08/2012

West Brom xxxxxx 19/08/2012 ( This is where the last row of West Brom on the 19 is so all data above this needs to be copied in rows of 500 and pasted into new child sheet,s then repeated for all Mother data tabs hope this makes sense ?

West Brom xxxxxx 20/08/2012

West Brom xxxxxxx 20/08/2012