Hi I am a new user please help me to split one file to many

[Ananthan]

Regards
Ananthan

[HansV]

Welcome to Eileen's Lounge!

Please provide more detailed information - we need to know how you want to split the file.
And which application are you using? Word, Excel, ...?

[Ananthan]

Using macros I need to split data in to seperate excel files

[Ananthan]

Interested to learn about macros in details

[Rudi]

Hi Ananthan,

In order to assist you with some code that you can use and learn from, we need an idea of your current file setup and how the current file should be split. Is it possible to upload a sample of your file (with dummy data) and provide steps on what should be done with it. For example, split the data into separate files at each sub total or at each heading or page break, etc...

We are happy to help and able to, but we cannot help you if you do not provide enough details on your file setup and what you need to do.

BTW: Welcome to the Eileen's lounge from me too.

Addition:

If you may just want to split the workbook you have so that each sheet in the workbook becomes a new file, you can run this code:

1. The split Excel files will be saved in the same folder as the current master workbook.
2. Press ALT + F11 in Excel, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module from the VBA menus, and paste the following code (below) in the Module Window.
4. Close the VBA window so you get back to Excel.
5. To run the code, press ALT + F8 to open the Run macro dialog. Select Splitbook, and choose Run.

The workbook will now be split into separate Excel files in the same folder as the original workbook.

Code: Select all

Sub Splitbook()
Dim sht As Worksheet
Dim MyPath As String
    MyPath = ThisWorkbook.Path
    Application.ScreenUpdating = False
    For Each sht In ThisWorkbook.Sheets
        sht.Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
        ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
        ActiveSheet.Range("A1").Select
        ActiveWorkbook.SaveAs _
                Filename:=MyPath & "\" & sht.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close Savechanges:=False
    Next sht
    Application.ScreenUpdating = True
End Sub

[Ananthan]

I need to split a file of of more than 100000, that need to be split in to seperate excel files using macros, that I was using in the previous company with the help of my collegue, but I don't know to record or write codes. like this needs to be circulated to branchwise for allocation incentives etc. From Pivot or somehing else as you will be knowing better than me this.

Thanks & Regards
Ananthan

[Rudi]

At what point in the records must the data be split into a new file? I highly doubt that you want to split each record to a new file.... (100,000 files.... )

Please identify how the data must be split? At a total, at a blank line, at a date or a title?
It will help if you attach a small desensitized sample of your file so we can see what you are working with.

[Ananthan]

The data is a single file that needs to be seperated branchwise, locationwise either.

The data which I am having is for enitire locations across India, I just need split into branchwise & send it to respective branches.

This is a routine excercise which we have to conduct daily, weekly & monthly as I am in to te MIS team.
Kindly do the needful.

Thanks & Regards
Ananthan

[HansV]

You will have to provide much more specific information if you want us to help you.

1) Do you have column headers (field names) in row 1? Or in another row
2) If column headers are not in row 1, in which row?
3) Which column contains the Locations?
4) Which column contains the Branches?
5) Are the data sorted by Locations/Branches, or are they in arbitrary order?
6) If the data are in arbitrary order, would it be OK to sort them?

[Ananthan]

Please find below :-
1) Yes in the first row
2)Not applicable
3)First Column, that I can alter in needed
4)That is not an Issue , branchwise is enough for me.
5)Yes sorted branchwise.
6)Not applicable.

Reagards,
Ananthan

[Rudi]

You can give this code a try...

Copy the code below
Open the workbook that contains the records to split
Press ALT+F11
From the Insert Menu of the VBA window, select Module
Paste the code onto this module
Close the VBA window
In Excel, press ALT+F8
Select the macro called: SplitByBranch
Click Run
For a more detailed process to integrate you macro, see this page.

Code: Select all

Sub SplitByBranch()
'---------------------------------------------------------------------------
'The code assumes the data to split is on the first sheet of this workbook!
'---------------------------------------------------------------------------
Dim sResp As String
Dim lC As Long
Dim rBch As Range
Dim rRng As Range
    
    sResp = MsgBox("The code will split the data on sheet 1 to new workbooks " & _
        "in the same path as the current workbook. Do you want to continue?", vbYesNo + vbExclamation)
    If sResp = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    Sheets(1).Copy Before:=Sheets(1)
    Set rBch = Range("A1", Cells(Rows.Count, 1).End(xlUp))
    Range("A1").Sort Key1:=Range("A1"), Header:=xlYes
    For lC = rBch.Rows.Count To 2 Step -1
        If Cells(lC, 1) <> Cells(lC - 1, 1) Then
            Set rRng = Range(Cells(lC, 1), Cells(lC, 1).End(xlDown))
            If Application.CountA(rRng) = 1 Then
                Set rRng = Cells(lC, 1)
            End If
            rRng.EntireRow.Cut
            Workbooks.Add
            ActiveSheet.Paste
            ThisWorkbook.Sheets(1).Rows(1).Copy
            Range("A1").Insert Shift:=xlDown
            Columns.AutoFit
            Range("A1").Select
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "/" & Range("A2").Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close False
        End If
    Next lC
    Application.DisplayAlerts = False
    Sheets(1).Delete
    Application.ScreenUpdating = True
    MsgBox "All records have been moved to new workbooks in the location: " & ThisWorkbook.Path & ".", vbInformation
End Sub

[Ananthan]

Thansk a ton team,

Looking to learn more & you have given me the macro codes, But I just want to know how to create the same for our use.

Is there any reference books available or through mail if can send me the soft copies if any, I will be always thankfull to yu.

Regards
Ananthan

[Rudi]

There are good books that you can purchase, but there is a wealth of free tutorials on the web that can give you a good introduction into VBA. If you work through some of these tutorials and put into practice what you learn, you will very quickly get the just of coding in Excel. It's really just a matter of "playing" with the code, recording macros and learning of the objects and hierarchies and exercise.

Start with these two online tutorials:
-- http://www.homeandlearn.org/index.html
-- http://excelvbatutor.com/vba_tutorial.html

And record macros in study up the code structures.

[Ananthan]

Thanks agian, its working fine,

I have also one more hepl required, posted seperately anyway but still I Need to send mails based on excel file which contains details.

In my previous company I used to generate OD letters ie; outstanding due letters using macros & mail merge.
In the data customer names mail id's branch amount all will be diffrent.
Kindly guide me to complete this please.....

Regards
Ananthan

[basha786]

Hi Rudi

With the help of same file, can we also split in MIS type which we normally do as pivot table.

Please advice.

[HansV]

With the help of same file, can we also split in MIS type which we normally do as pivot table.

Welcome to Eileen's Lounge!

What do you mean by 'MIS type'?

[HansV]

I have also one more hepl required, posted seperately anyway but still I Need to send mails based on excel file which contains details.

That has been asked and is being discussed in Need to Send bulk Mails.