Split data across multiple sheets in excel (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()
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
Download 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 addin let´s you split/categorize data from a sheet across multiple new sheets. The addin is made for Excel 2007.
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 addin 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 un-conditional money back guarantee for 14 days.
What about working with merged cells?
The addin 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 2007/2010 - Price $29 US
Related posts:
Quickly create links to sheets, tables, pivot tables and named ranges in a workbook
Consolidate sheets in excel, part 2
Excel udf: Sumif across multiple sheets
Recommended categories
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!