Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The macro copies airplane and model values into each new sheet.
Before:
After:
The Code
Sub Splitdatatosheets()
' Splitdatatosheets Macro
Dim rng As Range
Dim rng1 As Range
Dim vrb As Boolean
Dim sht As Worksheet
Set rng = Sheets("Sheet1").Range("A4")
Set rng1 = Sheets("Sheet1").Range("A4:D4")
vrb = False
Do While rng <> ""
For Each sht In Worksheets
If sht.Name = Left(rng.Value, 31) Then
sht.Select
Range("A2").Select
Do While Selection <> ""
ActiveCell.Offset(1, 0).Activate
Loop
rng1.Copy ActiveCell
ActiveCell.Offset(1, 0).Activate
Set rng1 = rng1.Offset(1, 0)
Set rng = rng.Offset(1, 0)
vrb = True
End If
Next sht
If vrb = False Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Left(rng.Value, 31)
Sheets("Sheet1").Range("A3:B3").Copy ActiveSheet.Range("A1")
Range("A2").Select
Do While Selection <> ""
ActiveCell.Offset(1, 0).Activate
Loop
rng1.Copy ActiveCell
Set rng1 = rng1.Offset(1, 0)
Set rng = rng.Offset(1, 0)
End If
vrb = False
Loop
End Sub
Get excel tutorial file
Remember to enable macros and backup your excel file because you can´t undo macros.
Split-data-across-multiple-sheets.xls
(Excel 97-2003 Workbook *.xls)
Split Data Across Multiple Sheets Add-In
Split data across multiple sheets Add-In for Excel let´s you split/categorize data from a sheet across multiple new sheets.
Features
- Select any single range
- Select specific columns
- Arrange output columns in any order
- Categorize data across sheets in current workbook, new workbook or any open workbook
- Add column headers to each new sheet
What you get
- Split data across multiple sheets add-in for Excel *.xlam file.
- Instructions on how to install.
- Instructions on how to use.
- Excel *.xlsx example file.
- 2 licenses, home and office computer.
Questions:
Is there a money back guarantee?
Sure, you have unconditional money back guarantee for 14 days.
What about working with merged cells?
The add-in won't work with merged cells. Unmerge cells before usage.
I have more questions?
Use this contact form to let me know.
Purchase Split Data Across Multiple Sheets Add-In for Excel - Price $19 US
Add in category
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Split values category
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
Excel categories
32 Responses to “Split data across multiple sheets [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
how would we print the sorted sheet into a new workbook rather than the same?
how do we set a range on the column. Say you only wanted to split the information between a4 and a10 rather than between a4 and the very end of the list.
Great - worked a charm.
“Split data across multiple sheets in excel (vba)”...
this code works well, but the prob is could not able to get the Merge cell as it is..
Thnx
Mashini,
I don´t have an answer right now.
I found this:
Excel often has problems copying ranges containing merged cells onto sheets with merged cells even if the merged areas are the same. The best solution is to not have merged cells. Next best is to unmerge all cells in the range that is to receive the copy before pasting in the new data.
from here
Hej.. works...problem is for a huge file.. doesn't. I have a 470k rows and is blocking at some point.
Alin,
Where does it halt and what does the row look like? You can use this contact form.
Awesome....
Thanks!
thnak you very much, its really helpful for me
It is really a nice article. I have used it in one of my automation but it goes into loop and never ends. I have to abruptly log off from the system to stop it. Need urgent help on it.
sujay,
can you provide an example of your sheet?
Hi,
I am new to Excel with VBA code.. I have 1 excel with 2 sheets ( A.xl contains 2 tabs Name and Rating ).
I want to read the datas from 2 sheets (Name and Rating) and appened the data in other excel file..
Can any one please help me to solve this...
Hi there!
This code is working perfectly for me besides the fact that the macro stops splitting my table into different sheets once it reaches a field name longer that 31 characters. Is there any way I can work around this?
Nice work...
Jojo,
Yes, I have uploaded a new file and changed the post.
Thanks for commenting!
Peter,
Thanks!
Lovely! Thanks so much Oscar.
dear sir
i have a file of 60000 row where i want to split in different sheet. each sheet will be 100 row i need a code in macro's.
Thanks for this code its a great help.
One question though: if I was adding more items to the original list how can I get it to just add these to the tabs rather than relisting the entire inventory again which ends up repeating records? I've been trying to add a "ClearContent" command in but I can't get it to work.
Any ideas?
Many thanks
Tom
Tom,
I am not sure I understand. Upload your file here.
Sorry If I wasn't being clear. Take your example, if you added a few more planes to your original list, then hit the button again, it runs through the macro again but rather than adding only the new additions to appropriate lists, it will relist the whole list (with the new additions) after the original list on each worksheet.
I worked around this by adding a remove duplicates instruction at the end of the instruction before it loops through to the next tab, but it would be good to know where I could have added something like a Clear.Contents, or Delete function, before the sheet was repopulated on pressing the button again.
Does that make sense?
Thanks
Tom
hi this code helped me lot but one thing i noticed is that formatting in other sheets are not as the sheet1 could you please suggest me how to fix the formatting
From where can i get Split Data Across Multiple Sheets Add-In after selecting the data
Sub SplitSelectionToSheetsByColumn() Dim regionToSplit As Range Dim columnToSplitBy As String Dim currentRow As Range Dim dataToSplitBy As String Dim sht As Worksheet Dim destinationSheet As Worksheet Dim lastCellOfSheet As Range Set regionToSplit = Selection columnToSplitBy = InputBox("Enter column to split by") For Each currentRow In regionToSplit.Rows dataToSplitBy = currentRow.Range(columnToSplitBy & "1").Value Set destinationSheet = Nothing For Each sht In Worksheets If (sht.Name = dataToSplitBy) Then Set destinationSheet = sht End If Next sht If (destinationSheet Is Nothing) Then Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = dataToSplitBy Set destinationSheet = ActiveSheet End If Set lastCellOfSheet = destinationSheet.Cells(destinationSheet.Rows.Count, "A").End(xlUp) currentRow.Copy lastCellOfSheet.Offset(1, 0) Next currentRow End SubLefteris,
Your macro works great! Thanks for posting!
This seems to be working for me but in our data files we always have the table starting with the header row first cell in A1. I haven't quite figured how to change the rages in the code to get it to properly copy the header row to each of the sheets it creates? It would seem this should only require changing the named ranges?
Hey, many thanks for this very useful macro, I have adjusted it to my work... whats the deal after this and why i need help in it?
Imagine that i just want to use all macro in the first time to create and divide multiple sheets
After that i just need to use it again in the same sheet, for example, for new data in f4:i4 and to split it for the sheets created in the first time.
sorry for my english and many thanks in advance.
when i clik on split every time it split
i want to slip/updata data only once but here every time data slpt which already slipt
Hi
I have a worksheet, I want to add a value to first data of first column my worksheet and then to split data upon on this operation into other worksheets.
Thank you, Mr. Oscar for the code!
You save our life and you make our life easier because we almost fall into the trap of being manual excel labour.
XOXO
Wish you a healthy and happy life!
Rizka & Syafira
Thank you for commenting, I am happy it saved you time.
Hii , in ur list there is 3 type of airplanes, and the split sheet do to all types
How can I split for 2 type that I choose of air plan and not 3
Thanks?