Hi I am a new user please help me to split one file to many
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Hi I am a new user please help me to split one file to many
Regards
Ananthan
Ananthan
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hi I am a new user please help me to split one file to m
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, ...?
Please provide more detailed information - we need to know how you want to split the file.
And which application are you using? Word, Excel, ...?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
Using macros I need to split data in to seperate excel files
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
Interested to learn about macros in details
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Hi I am a new user please help me to split one file to m
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.
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
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
Thanks & Regards
Ananthan
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Hi I am a new user please help me to split one file to m
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.
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hi I am a new user please help me to split one file to m
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?
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?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
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
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
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Hi I am a new user please help me to split one file to m
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.
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
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
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
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Hi I am a new user please help me to split one file to m
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.
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 26
- Joined: 23 Apr 2014, 06:05
Re: Hi I am a new user please help me to split one file to m
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
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
-
- NewLounger
- Posts: 1
- Joined: 26 Apr 2014, 10:09
Re: Hi I am a new user please help me to split one file to m
Hi Rudi
With the help of same file, can we also split in MIS type which we normally do as pivot table.
Please advice.
With the help of same file, can we also split in MIS type which we normally do as pivot table.
Please advice.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hi I am a new user please help me to split one file to m
Welcome to Eileen's Lounge!basha786 wrote:With the help of same file, can we also split in MIS type which we normally do as pivot table.
What do you mean by 'MIS type'?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hi I am a new user please help me to split one file to m
That has been asked and is being discussed in Need to Send bulk Mails.Ananthan wrote:I have also one more hepl required, posted seperately anyway but still I Need to send mails based on excel file which contains details.
Best wishes,
Hans
Hans