Split sheet to different files - variable number of rows

[tohocasu]

Hello, i need help with spliting excel database. It contains about 5000 rows in 11 columns with first row a headline and free rows between records.

Task: to split the database into separate files = one file is one record (number of rows variase from 1 to 368). it would be great if each file could contain the headline in first row and has a SUM in the end of seventh column ("I"). It is possible that i have to do this repeatly so i would prefer Macro solution, but if you know something different, let me know.

I am a foreigner so sorry for my English Really thx for help

[Tinbendr]

Can you supply a sample workbook?

[tohocasu]

I am sorry I forget to upload sample. Here it is, i changed only the names and length, because original file had few thousands rows. Hope this will help

[BrianMH]

Hi,

Are you wanting these split by ID FK? Also is there a reason you couldn't use access? For databases access is much more suited.

[tohocasu]

Yes, by ID FK. Access would be great, but I am not making this database, just once in a time I get it from our client ih this excell a need to separate the data. If there is a way how to do it in Access I am willing to try it

[BrianMH]

If you were to import the data into access then you could use queries to separate view it in different ways and get different totals.

[tohocasu]

Yes, that would help, but only with the totals and not with spliting into separate files. I should add that I need the separated files to sent further, so the spliting is crucial and I am not sure if that is possible in access.

[BrianMH]

Well you could use the queries to export from so it is possible.

[BrianMH]

To clarify are there a limited number of 'ID FK' values? Do you know what all these will be in advance?

[tohocasu]

could you please post me an istructions for the queries, because I am not sure what it is.

No I dont know how many of them will be in next file and the values of ID FK is not limited, because our client had their own system and sometimes it starts with number 1 or 5 and so on. Only the number of letters stays the same.

[Tinbendr]

I'm pursuing the separate file option.

Do you need to save these files to a specific filename?

E.g. 2012-05-15 WB 1.xls

Or maybe the ID FK #?

[VBA]Option Explicit
Sub SplitDB()
Dim WB As Workbook
Dim NewWB As Workbook
Dim WS As Worksheet
Dim LastRow As Long
Dim A As Long
Dim Counter As Long
Dim CRNumRows As Long
Dim nLR As Long
Dim Filename$
Set WB = ActiveWorkbook
Set WS = WB.Worksheets(1)
Counter = 1
With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For A = 2 To LastRow
Set NewWB = Workbooks.Add
If A = 2 Then
.Range("A1:K1").Copy Destination:=NewWB.Worksheets(1).Range("A1")
.Range("A" & A).CurrentRegion.Copy Destination:=NewWB.Worksheets(1).Range("A1")
Else
.Range("A1:K1").Copy Destination:=NewWB.Worksheets(1).Range("A1")
.Range("A" & A).CurrentRegion.Copy Destination:=NewWB.Worksheets(1).Range("A2")
End If

CRNumRows = .Range("A" & A).CurrentRegion.Rows.Count
With NewWB.Worksheets(1)
nLR = .Cells(.Rows.Count, 1).End(xlUp).Row
'A bit of trickery to get Excel to recognise Text as Numbers.
.Range("L1").Value = 1
.Range("L1").Copy
.Range("I2:I" & nLR).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
.Range("I" & nLR + 1).Formula = "=SUM(I2" & ":I" & nLR & ")"
.Range("I" & nLR + 1).Select
'Filename$ = Format(Date, "YYYY-MM-DD") & " " & Counter
Filename$ = .Range("A2").Value & " " & Format(Date, "YYYY-MM-DD")
Counter = Counter + 1
NewWB.SaveAs Filename$, FileFormat:=xlDefault
NewWB.Close
A = A + CRNumRows + 2
End With
Next
End With
End Sub
[/VBA]

[tohocasu]

It is great i would hug you (in case you are woman) or shake your hand (if your a man) if you would be here

really thx, you saved me hours of work