E-Mail 'Split data across multiple sheets - VBA' To A Friend
Email a copy of 'Split data across multiple sheets - VBA' to a friend
Loading ... Loading ...68 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.
Oscar,
I do remember seeing one nice way of populating a table with the use of vba such as :
Sub EnterName()
Dim col, Lrow As Single
Dim tmp As String
col = Application.WorksheetFunction.Match(Range("C2"), Range("B4:H4"), 0) - 1
tmp = Range("B" & Rows.Count).Offset(0, col).Address
Lrow = Range(tmp).End(xlUp).Row
Range("B1").Offset(Lrow, col).Value = Range("E2").Value
Range("B1").Offset(Lrow, col + 1).Value = Range("F2").Value
Range("E2").Value = ""
Range("F2").Value = ""
End Sub
How would it be possible to modify the code to populate a table such as: the first column header could be chosen from the drop-down list as well as the first row header. In other word the location of the data to be entered could be determined by the row AND the column.
Thanks to share your opinion.
Cyril,
What is the value in cell C2 and the values in cell range B4:H4?
C2 should be a data validation (list).
B4:H4 (here only 7 columns) would be the headers to match the value in C2.
A second data validation should make reference to Column A.
Hence:
1st data validation correlated to Column's Headers (B to ect)
2nd data validation correlated to values in Column A ("Row Header")
As for the kind of values, being headers they would most likely be (but not limited to) text strings.
Cyril,
See this post: Add values to a table (vba)
Could you please show me the code to place the copied data into a different tab instead of below the input cells. Its annoying me and I'm rubbish at this. thanks.
phil,
see this post:
Add values to different sheets (vba)
i tried to add a list number 4 with this formula =IF(D1>3,INDEX($A$2:$A$21,(ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-4)),"")
the value at f4 is DD but the f5, f6,etc....arejust repeating the result
Fahmy,
split-values-into-groups-using-excel-formula.xlsx
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.
Hi Oscar, thanks for the great tutorial.
I was hoping to use what you've described above but for data that stretches across a single row.
For example, if I have data that stretches from A1:J1, I would like to split it up into 5 rows so that I would have the values appear in A2:B2, A3:B3, A4:B4,A5:B5, A6:B6.
I have taken a stab at manipulating the formulas you've provided but with no luck. Was hoping you could help out!
Thanks!
Hasan,
Array formula in cell range: A2:E3:
How to create array formula
1. Select cell range A2:E3
2. Paste formula in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
Adjust bolded cell ranges if you enter the array formula in a different cell range.
Hasan1.xlsx
[...] a previous post I described how to simplifiy data entry. Now it is time to put values in separate [...]
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!
Hi Oscar,
Is there any way I can automatically populate the entry of one column base on the value of the other the value will be from the list
sample list:
Category Values
A a.1
a.2
a.3
B b.1
b.2
so on...
when i choose Category A in the cell A1 the the cell b1-b3 will be populted base on the values.
Thansk,
Hi,
First of all thanks its a very great Macro.
But i would like to do a little modification.
What should i change, to copy the values, to the first row, and pushing down the other datas, and not after the last one?
i guess somewhere here:
.Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
Thanks:
Márton
you need to for i loop
Can you provide the code for Add values to different sheets (vba)
vivek,
It is already on this webpage, below the heading Macro code.
Here it is again:
Sub AddValues() Dim i As Single i = Worksheets("" & Range("D2")).Range("A" & Rows.Count).End(xlUp).Row + 1 Worksheets("" & Range("D2")).Range("A" & i & ":C" & i) = _ Worksheets("Enter Data").Range("A2:C2").Value Worksheets("Enter Data").Range("A2:C2") = "" End SubThis 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?
How do I automate a data entry for example
teacher- $50
Pets-$80
where as the pet is always $80 and the teacher is always $50
How would I enter or put a formula in for excel?
Sarah,
The lookup table is in cell range D1:E3.
is it possible for add values to different workbooks like add values to different sheets (vba)? can you send me the example file. thanks.
[…] are great for doing repetitive tasks. Two years ago I wrote a post about transferring data to worksheets. It is about automatically moving data to a worksheet you […]
would love to see your post about transferring data to worksheets! I would like to parse out a huge data file into seperate tabs, automatically.
I could not make the VBA for automated data entry to work.
So I figured out formula that works for me:
=IF(ISBLANK(A1)," ",NOW())
you just copy it down in the column you want the automated date and time, also you need to format the cell to custom:
d-mmm-yy-hh:mm AM/PM
or any date and time combined format.
and by the way Oscar is a genius, this blog saved me so much stress it is unbelievable.
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.
Thank you for this. I am new to Excel and I have created a workbook that has no macros so far but I am looking for something very similar to this vba code and especially like how you included the dates and how the code gets copied to new rows on the year sheet each time the "Add" button is pressed on. I am wondering how to change the code slightly for the following application: Is there a way to copy different cells (not a range) in a single column ie: A2,A3,A4,A8,A10 on the "Enter Data" sheet to the year sheet in columns (ie: D3,D4,D5,D6,D7) rather than rows? Also, I see after the "Add" button is pressed it removes the values from the cells. Can the code be updated to not clear the cells? Thanks ny information you can provide.
Oops, I made 1 mistake in my last comment. I would like to copy a date from f12 along with data from A2,A3,A4,A8,A10 but want the date transferred to the year sheet in the same column as the date D3(Date),D4,D5,D6,D7,D8.
HI Oscar! I would need your help to strech column A to 500 rows with 8 lists. Thanks!
Hey, quick question, would this beable to expand into say a list of 100 values and have 10 groups. ive tried editing the code but am unsuccessful in getting the formula to work correctly
Kerien
Here is a workbook for you:
split-values-into-groups-using-excel-formulav2.xlsx
Hi Oscar, I wonder if you could help, I have a large number of record numbers (basically a call list for a sales office) which I need to split up into equal lists, there could be between 100 and about 400 items.
I have tried to use your example spreadsheet and replace the ranges with dynamic named ranges (which are set up contain all the record numbers), but that gives me a #VALUE error, even if I use crtl+alt+enter.
In addition, I cannot seem to make the lists in your example longer to accommodate this, if I copy and paste the formula downward, I get one record number repeated.
Here is a link to my spreadsheet on google drive (I am using excel 2013 - just using google drive to host the file!) I would really appreciate it if you could have a look and let me know where I'm going wrong...
Link
Cheers!
Joe,
I have built a user defined function that is easier to use, read this post again.
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 Oscar, Thank you so much, this is much easier, thankyou!
Hi Oscar! Thank you so much for this tutorial. I have been trying to replicate this exact workbook for 10 groups, and also use the "CHOOSE" AND "RANDBETWEEN" functions to randomize the values of column A into the array but having a bit of trouble doing so. Would this be possible to set this up? Really appreciate your help!
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.
I am trying to use your formula (the spreadsheet file) to create divide 253 students (value) into 15 groups equally. I tried to edit the code...but I'm getting an error message when trying to adjust the array.
Tamara Smith,
If you are trying to expand the array formula and get an error, try this:
The array formula in column C is entered in cell range C4:C25.
Select cell your new cell range, example C4:C27
Press with left mouse button in formula bar.
Press and hold CTRL + SHIFT. Press Enter to create a new array formula for your new range.
I recommend you use the User Defined Function, it is much easier to work with.
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.
Hi Oscar,
first of all thank you for all the awesome and detailed tutorials.
I would like to add data to a table, using the method you describe. How can I, using vba, add an empty row at the end of the table and copy the new data there?
Thank you
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?
Hello
i really appreciate your blog its very helpful.
however my is issue is i do not want to list the names. i want to know how many PEOPLE should be in each group. so i i have levels and each level has a specific number of people.
https://postimg.cc/nXbTRzpd
and based on the number OF PEOPLE they will be divided equally into N number of groups (1 group, 2 groups , .... up to 8 groups some times)
https://postimg.cc/9DQzjPmz
so i will have the table display horizontal with number of people in each group.
https://postimg.cc/s13Q3GJF
how can i do that?
bu örneği excel 2016 ya göre tek sütunda nasıl yapılır yardımcı olurmu sunuz