How to split data into multiple worksheets based on column in Excel?

Supposing you have a worksheet with huge rows of data, and now, you need to split the data into multiple worksheets based on the Name column (see following screenshots), and the names are entered randomly. Maybe you can sort them first, and then copy and paste them one by one into other new worksheets. But this will need your patience to copy and paste repeatedly. Today, I will talk about some quick tricks to solve this task.

doc-split-data-by-columns1



-2



doc-split-data-by-columns2
doc-split-data-by-columns3
doc-split-data-by-columns4
doc-split-data-by-columns5

Split data into multiple worksheets based on column with VBA code

Split data into multiple worksheets based on column with Kutools for Excel


arrow blue right bubble Split data into multiple worksheets based on column with VBA code


If you want to split the data based on column value quickly and automatically, the following VBA code is a good choice. Please do as this:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

 

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1        
Set ws = Sheets("Sheet1")        
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"            
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Note: In the above code:

  • vcol =1, the number 1 is the column number that you want to split the data based on.
  • Set ws = Sheets("Sheet1"), Sheet1 is the sheet name that you want to apply this code.
  • title = "A1:C1", A1:C1 is the range of the title.

All of them are variables, you can change them as your need.

3. Then press F5 key to run the code, all data in the active worksheet are split into multiple worksheets by the column value. And the split worksheets are named with the split cell names. See screenshot:

doc-split-data-by-columns6

Note: The split worksheets are placed in the end of the workbook where the master worksheet is in.


arrow blue right bubble Split data into multiple worksheets based on column with Kutools for Excel

As an Excel beginner, this long VBA code is somewhat difficult for us, and most of us even don't know how to modify the code as our need.

Here, I will introduce you a multifunctional tool--Kutools for Excel, its Split Data utility not only can help you to split data into multiple worksheets based on column, but also can split data by rows count.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

If you have installed Kutools for Excel, please do as follows:

1. Select the range of data that you want to split.

2. Click Enterprise > Worksheet > Split Data (or Enterprise > Split Data ), see screenshot:

3. In the Split Data into Multiple Worksheets dialog box, you need to:

1). Select Specific column option in the Split based on section, and choose the column value which you want to split the data based on in the drop-down list. (If your data has headers and you want to insert them into each new split worksheet, please check My data has headers option.)

2). Then you can specify the split worksheet names, under the New worksheets name section, specify the worksheet names rules from the Rules drop down list, you can add the Prefix or Suffix for the sheet names as well.

3). Click the OK button. See screenshot:

doc-split-data-by-columns8

Now the data are split into multiple worksheets in a new workbook.

doc-split-data-by-columns9

Click to know more about this Split Data feature.

Related article:

How to split data into multiple worksheets by rows count?

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +2 Jagadesh.K
Split data into multiple worksheets based on column with VBA code shows some error. please try to rectify it & update the same. If you provide the sample excel files it will be really helpful.
2013-11-17 09:41 Reply Reply with quote Quote
Permalink +1 Georges
Hi,
thanks for the code it works for me!

I am trying to find a code that split one master sheet into multiple sheet based on date
2016-08-24 11:27 Reply Reply with quote Quote
Permalink +1 Heidi
That was incredible! This process would have taken me over an hour to do, but it was done within 30 seconds. This one I will keep for my VBA library. Thank you!
2017-02-06 13:06 Reply Reply with quote Quote
Permalink +1 Cindy
I get an error when pressing F5 - GoTo Box asking for a reference??
2013-12-06 18:12 Reply Reply with quote Quote
Permalink +6 Niki
The VBA process worked perfectly, thank you so much for sharing your expertise and saving me a lot of time!
2014-01-03 02:48 Reply Reply with quote Quote
Permalink 0 Vickey
The VBA code worked perfectly. It does not seem to update the sheets as changes are made to Sheet1. Please assist.
2014-01-05 09:49 Reply Reply with quote Quote
Permalink +2 Pieter
Hi,
Thanks for the code it works for me!

I only have two questions/remarks.

1 the copied data does not inlclude the layout of the original file. Would it be possible to copy the data as table with autofilter?

2 the copied data does not seem to be bounded to the title range.
Is it possbible to adjust the code for a specific range or tablename?

These adjustment would be very helpfull.

Regards,

Pieter
2014-01-24 11:42 Reply Reply with quote Quote
Permalink +3 Belinda Martinez
Works like a charm! Thank you.
2014-02-04 15:04 Reply Reply with quote Quote
Permalink +1 Bobby M
Works like a charm...

Thank you for premium code... :lol:
2014-02-17 10:21 Reply Reply with quote Quote
Permalink +1 Jonathan
Thank you so much, this worked great. However, what hapens if I want the data within each tab sorted again (using anther column)? Basically, this VBA breaks it into tabs but I may want it broken down further..is that possible?
2014-02-20 18:33 Reply Reply with quote Quote
Permalink 0 Ben
Hi Jonathan,

Old comment I know, but might be helpful for others in the future:

I needed to do this but couldnt find an easy way with the VBA. However, I found that if you make a new Column in your Spreadsheet as an amalgamation of the 2, so eg

=A1&" "&A2

This then gives you 1 Cell with both sets of information. You can then run the above module and it works fine!

Edit - The data in the collumns needs to be shorter than 30 Characters, otherwise the data is not copied across (shows as an error on the Module) and you get a blank sheet in the middle of your new Sheets.
2016-08-17 09:29 Reply Reply with quote Quote
Permalink +1 Aditi
Awesome.. This was stunning. I was struggling with this problem for so long and this code came in as a respite.

Thanks for sharing it.
2014-02-26 03:56 Reply Reply with quote Quote
Permalink +2 Gopinath
Amazing. Thanks for posting.
2014-03-13 18:41 Reply Reply with quote Quote
Permalink +4 MieMie
The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.
2014-03-20 14:51 Reply Reply with quote Quote
Permalink +1 Abel Solomon
The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.
What I'm not sure is: What's the max number of rows the macro can support? I could play around with it... It's somewhere between 20k and 40k!
2016-02-27 10:54 Reply Reply with quote Quote
Permalink 0 Shrikant
Facing same issue.
Code works for sheets where data is less rows, however for larger data it shows error as "Excel cannot complete this task with available resources. Choose less data or close other applications" (there is no any other application working at same time)

The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.
2017-05-09 13:03 Reply Reply with quote Quote
Permalink +1 Bekki
You are my all time hero! I have hunted for this for months with no luck. I have to do this weekly/monthly breaking reports into 147+ worksheets and they wont get me kutools.

On that note.. I really need to learn coding. :(

But thank you!
2014-03-20 15:54 Reply Reply with quote Quote
Permalink +1 ACE
HI ,
I have a sheet having 65000 Records and having 8- different cases,
so basically it should generate 80 different sheets. I tried running this code, but it throws Runtime Error 6 Overflow.
Can this code be tweaked to solve my problem? Please ur help will be highly appreciated.
2014-04-24 08:39 Reply Reply with quote Quote
Permalink +6 JD
Quoting ACE:
HI ,
I have a sheet having 65000 Records and having 8- different cases,
so basically it should generate 80 different sheets. I tried running this code, but it throws Runtime Error 6 Overflow.
Can this code be tweaked to solve my problem? Please ur help will be highly appreciated.


Try changing Dim vcol, i As Integer to Dim vcol, i As Long
2014-05-08 14:45 Reply Reply with quote Quote
Permalink 0 Mustafa
Hi I tried changing DIM vcol to LOng and it was working good but suddenly it through an error there isn't enough memory to complete this action try using less data or closing other applications.

Though I don't have any other applications open. I'm having little more than 100 k rows and approx. 16 mb file size. any help would be appreciate.

Thanks
Mustafa
2016-07-27 16:43 Reply Reply with quote Quote
Permalink 0 Croftycooke
Awesome bit of code - works perfectly (if you change the variables to the one your spreadsheet needs)
2014-04-30 12:11 Reply Reply with quote Quote
Permalink 0 Ahmed Sarheed
I have a sheet with a variable number of rows. One of the columns is the dates ranging from 2010 onwards. The other columns are fund names with the NAV data for each fund against the date. So I do not want to split the columns into different sheets, I want to split each FUND NAME into its own sheet with the NAV data per End Of Month date, not the daily dates.

Can this be done or is this impossible?
2014-05-01 08:06 Reply Reply with quote Quote
Permalink +2 Tim
I have a worksheet that i use and i'm trying to find a vba code that will reconize a account name and copy the paticular row to a new workbook and sheet with the same name can you help?
2014-05-06 13:24 Reply Reply with quote Quote
Permalink 0 Starscor
Great! The VBA code is working, thanks!
I need these output worksheets to be in individual excel files instead of worksheets and there is error when i split to many many worksheets.
2014-06-02 03:59 Reply Reply with quote Quote
Permalink 0 kokoliso
Starscor and Tim if you want to split the sheets of the file into several files using the names of the rows there is a small macro code in this same web page that does it, just search for "split a workbook to separate Excel files" you will find it.

Add the code of that example to the end of this one deleting the duplicate end sub and sub of course and you will get one file for each.
2014-06-05 16:42 Reply Reply with quote Quote
Permalink 0 yash
can anyone help me how to sort the columns in different sheets in the same workbook at a time and also to remove duplicates in different sheets as I have around 65 sheets in the same workbook
2014-06-12 11:07 Reply Reply with quote Quote
Permalink 0 Theresa
this is so exciting! Thank you. I've been lookign for this for some time.
2014-06-16 17:39 Reply Reply with quote Quote
Permalink 0 Frankie
Excellent - thank you for sharing this. Even propagates highlights/form at to new worksheets!
2014-06-17 02:17 Reply Reply with quote Quote
Permalink +2 Sebghatullah
Hi
Thank you very much from sharing the code.
I have a problem with my array value. for example my value is Cash / Debate. it errors or does not split the data because the sheet name is wrong. so i want to a code to split data and rename sheets by "Data"& worksheets(sheets.count).
please help me it is very important for me.
I search Google but not successfully. I apologize, because my English is very weak. thank you I am wanting for your nice reply Thanks a lot.
2014-06-25 04:54 Reply Reply with quote Quote
Permalink 0 Melissa
Works great! I've been searching for months for this code and how to tweak it to fit my needs and you did it! I do want to ask is there a way to get it to only repeat a few cloumns into the new sheets? I don't need all the data copied just part of it. Thanks again!!
2014-07-11 16:58 Reply Reply with quote Quote
Permalink 0 AC
Thanks for this! Great macro!
2014-07-30 19:57 Reply Reply with quote Quote
Permalink 0 Fred
Great macro, many thanks! :-)
2014-08-01 13:21 Reply Reply with quote Quote
Permalink 0 caparteekjain
Thank you so much. The coding really works. I really loving it.
Thank u so so much.

But can anybody tell me the way to split various worksheets as seperate excel file.
IT anybody can tell then it will be very thankful.
2014-08-03 16:39 Reply Reply with quote Quote
Permalink +3 Sebghatulbary
Hi caparteekjain
you can use the following code to save each sheet as a separate file.

MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & sht.Name & ".xls"
ActiveWorkbook.Close savechanges:=False
Next sht
2014-08-05 05:28 Reply Reply with quote Quote
Permalink 0 Donuts
Splitter brilliant, this saving to separate file though, would you create this as a separate module? Also can you confirm which if any parts of the code need to be amended to reflect the circumstance of the sheet or where you're saving it?
2014-08-08 13:45 Reply Reply with quote Quote
Permalink 0 veve3
Where do I put this code? I need Separate workbooks. Thank you very much
2015-03-17 09:15 Reply Reply with quote Quote
Permalink 0 jimmi_10
Hi Sebghatulbary,

I added this bit to my Macro but I am not sure where each of the files have saved. Do you know where they go?
2015-10-28 04:54 Reply Reply with quote Quote
Permalink 0 jimmi_10
Hi Sebghatulbary,

I just added this code to my Macro, but I am not sure where the files have actually saved. Do you know where they would save?
2015-10-28 04:54 Reply Reply with quote Quote
Permalink +2 Dinks
This code has saved 1000's of manhours across world. Please guide how to create a VBA for sending this individual file to a particular person mentioned in the sheet. The email address is added in the data as column against the data.

Eg: A1:A10 = Oil - This has to go to person z whose email address is mentioned in R1:R10
A11:A20= Pulses- This has to go to person y whose email address is mentioned in R11:R20

so on and so forth....
2017-04-06 01:07 Reply Reply with quote Quote
Permalink +3 Katie
Thank you so much for this. The way the macro splits now, it's making sheets for rows that are still considered header rows in my worksheet.Is there a way to change the code so that the header is row 1 through 13?
2014-08-13 12:54 Reply Reply with quote Quote
Permalink +1 kana
hi guys

I am seem to get an error onth eline " for I = 2 to Ir" what is this referring to .
2014-08-19 08:59 Reply Reply with quote Quote
Permalink 0 Umesh
thank you. It worked after I did minor changes to the code.
2014-09-17 01:38 Reply Reply with quote Quote
Permalink +5 Chloe
Hi, when i tried to run the macro after pasting the script and amending the necessary, an error was prompted "type mismatch" and when I click in to debug, below line was highlighted..

For i = 2 To UBound(myarr)

How should I recitify this error please?

Appreciate your advice.
2014-09-23 10:30 Reply Reply with quote Quote
Permalink +1 Lord Ben
Work nicely, for the first 10,712 lines... Then it broke XD
Apparently my computer isn't powerful enough.
2014-10-14 07:59 Reply Reply with quote Quote
Permalink 0 Lisachea
Thank you so much for this feature. I've wasted a large amount of my time cutting and pasting.
2014-10-27 18:21 Reply Reply with quote Quote
Permalink 0 Patrick
Hi,
This works great with a one line header but my sheet has a 10 line header. I tried changing the title definition to
title = "A1:L10"
and
title = "1:10"
neither worked.

Thanks
2015-01-15 02:05 Reply Reply with quote Quote
Permalink 0 Sarah
try title = "A1:A10"
2015-08-06 20:16 Reply Reply with quote Quote
Permalink 0 Eric
Hi
You probably got it cracked
You need to change the value down the bottom of the code where it says . range ("A3") to in your case ("A10") this is the location of the copy command.
Hope that helped
2016-10-13 20:46 Reply Reply with quote Quote
Permalink 0 Allan
change the following row:

ws.Range("A" & titlerow & ":A" & lr).EntireRow.C opy Sheets(myarr(i) & "").Range("A1")

you will want:

ws.Range("A" & "A1:L9" & titlerow & ":A" & lr).EntireRow.C opy Sheets(myarr(i) & "").Range("A1")
2017-02-15 19:36 Reply Reply with quote Quote
Permalink 0 Patrick S
I cannot get this to transfer more that one row for the header.
I need the first 10 rows transferred to each sheet. I have tried changing title = "A1:C1" to: title = "A1:L10" and title = "1:10" but it still just transfers the first row.
Please help. Thank you
2015-01-15 16:13 Reply Reply with quote Quote
Permalink +1 dnyaga
Great stuff,Have been doing this manually.Will save alot of time
2015-01-29 08:17 Reply Reply with quote Quote
Permalink 0 Andrew Hope
Works perfect! But what if I want to add totals to each of the newly created sheets, given that each sheet has a different amount of rows.
2015-01-31 14:12 Reply Reply with quote Quote
Permalink 0 cheryle
Do you have a code for splitting by row header instead of column?
2015-02-08 16:53 Reply Reply with quote Quote
Permalink 0 rajesh t
Working Perfect, Need know how copy only the few columns?
2015-02-24 07:46 Reply Reply with quote Quote
Permalink 0 Prithwiraj Kundu
it s of a great help. thanks a lot for the code and example

However I need the data to split based on other than column A( as shown in the example)it may be based on column B,C,D,... what would be the difference in coding in that case ?
2015-06-29 12:28 Reply Reply with quote Quote
Permalink 0 Deanna
change vcol to be the number corresponding to the column, example, column B would be 2
2015-07-13 17:12 Reply Reply with quote Quote
Permalink +1 Yzel
Quoting MieMie:
The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.




I have same issue as above.appreciat e all your help please
2015-07-15 15:38 Reply Reply with quote Quote
Permalink 0 Marissa
Code works great on a small sample set of data but when I run it on my large data set (7000 rows and 53 columns) it creates the correct worksheets with no data on them. Any ideas?
2015-07-31 21:04 Reply Reply with quote Quote
Permalink 0 Ellen
This is great!

Can the same code be used to pull data from two worksheets? For example, I have a workbook with work completed and another with amount to bill. I'd like to combine the two together on a worksheet for each company with work completed on top and amount to bill below.

Any help is greatly appreciated, thanks!
2015-08-05 22:35 Reply Reply with quote Quote
Permalink 0 mona
IF I am trying the code for huge data, it is showing runtime error in "For i = 2 To lr" line.
Please help me with this
2015-08-12 12:22 Reply Reply with quote Quote
Permalink 0 Amanda
I ran into the same issue. I found that if I cut down the size of my table (It went from around 40,000 to about 20,000 rows) I no longer got the error.

What I'm not sure is: What's the max number of rows the macro can support? I could play around with it... It's somewhere between 20k and 40k!
2015-11-04 19:44 Reply Reply with quote Quote
Permalink 0 McMoeLee
Really great Work!! Helps me a lot!
Copy, Little adapt for my needs, works like a charm!
2015-08-26 11:20 Reply Reply with quote Quote
Permalink 0 MF
Hello

When I try to use this it only copies the title into each new workbook. What do I need to change in order to have all rows associated with a given column value into the new worksheets. I have Data in Columns A2 through N3200
2015-09-10 21:58 Reply Reply with quote Quote
Permalink +2 Rhys
Hi

This code works perfectly for seven tabs, however after that I have the following error "excel cannot complete this task with available resources choose less data or close other application"

my data on sheet1 is A-E with 16133 rows.

Is this due to my pc only running 4gb ram?
2015-09-18 12:25 Reply Reply with quote Quote
Permalink +1 Henrik
Hi,
the code works fine for most of the data. However, some sheets are created without the title and without data. This happens without a (for me) visible pattern. The sheet is created but the entire range of data is simply not copied into the sheet and the sheet name is e.g. Sheet4 if is it the 4th sheet. When it happens, it happens for the entire range of that specific title area.
It doesn't stop the process, as some sheets are correctly created later during the macro...

Screendump: https://www.dropbox.com/s/xnjcurt4ub01d1j/Screen%20Shot%202015-10-02%20at%2017.27.28.png?dl=0
2015-10-02 15:30 Reply Reply with quote Quote
Permalink 0 John
I had the same issue and it looks to me like the number of characters in the cells that were skipped might be too long? Anyone think that might be the problem??

Quoting Henrik:
Hi,
the code works fine for most of the data. However, some sheets are created without the title and without data. This happens without a (for me) visible pattern. The sheet is created but the entire range of data is simply not copied into the sheet and the sheet name is e.g. Sheet4 if is it the 4th sheet. When it happens, it happens for the entire range of that specific title area.
It doesn't stop the process, as some sheets are correctly created later during the macro...

Screendump: https://www.dropbox.com/s/xnjcurt4ub01d1j/Screen%20Shot%202015-10-02%20at%2017.27.28.png?dl=0
2015-10-13 21:31 Reply Reply with quote Quote
Permalink 0 Henrik
Hi John - You are right!
It was the length/number of characters in the cells that are used as sheet-names... i shortened them a bit (to 30 I think) and then the problem disappeared.
:)
2015-10-22 08:57 Reply Reply with quote Quote
Permalink 0 Beth
thanks, works great!
2015-12-04 13:49 Reply Reply with quote Quote
Permalink 0 sai
how to remove the header ? from the code ? which one i have to delete please help
2015-12-09 14:48 Reply Reply with quote Quote
Permalink 0 satya sai
can some one help how to delete heading from this code
2015-12-09 15:07 Reply Reply with quote Quote
Permalink 0 NK
HI!

Your codes is running perfectly, Really thanks for the code,
Its splitting the data but I've a total colum there in each sheets,

Can you tell me how to handle that? while inserting data, a new data overrides the existing Total values so I'll need to insert a new before splitting the data.

Can you help me in that?

Thanks.
2016-01-13 11:02 Reply Reply with quote Quote
Permalink 0 Judalyn
The code seems o be working fine, however I am getting blank sheets for others, please help
2016-01-17 06:58 Reply Reply with quote Quote
Permalink 0 John
Anybody know how to modify this code so that the formulas are carried over to the new sheets rather than just values?
2016-01-19 22:20 Reply Reply with quote Quote
Permalink 0 Jelena
Quoting John:
Anybody know how to modify this code so that the formulas are carried over to the new sheets rather than just values?

I need to preserve formulas, too. Thank you if any body could help.
2016-03-02 09:10 Reply Reply with quote Quote
Permalink +1 Jelena
Thank you!!!!! Thank you!!!! You are my heroe :)
2016-02-19 12:35 Reply Reply with quote Quote
Permalink 0 July
Hi,
Thank you for the code, it was very useful and minor chances have been made to match my situation. Can you please give me a suggestion regarding the cell dimension shown in master sheet? Multiple sheets were created, master sheet was hidden, but the cell dimension wasn’t copied to the other sheets.
Thank You,

Julia
Thanks for the code it works for me!

I only have two questions/remarks.

1 thQuoting Pieter:
Hi,
Thanks for the code it works for me!

I only have two questions/remarks.

1 the copied data does not inlclude the layout of the original file. Would it be possible to copy the data as table with autofilter?

2 the copied data does not seem to be bounded to the title range.
Is it possbible to adjust the code for a specific range or tablename?

These adjustment would be very helpfull.

Regards,

Pieter


Is it possbible to adjust the code for a specific range or tablename?

These adjustment would be very helpfull.

Regards,

Pieter
2016-03-04 09:30 Reply Reply with quote Quote
Permalink 0 Tom
The code works great for names and titles, but does anyone know how to separate the column into new tabs based on date? What do I have to change in the macro?
2016-03-11 17:24 Reply Reply with quote Quote
Permalink 0 Nadeem Iqbal
Thanks a lot for sharing a much needed programming code.

Can you please tell me how to process the entire code for just carrying out the same process on just the last filled/data row?
2016-04-01 03:26 Reply Reply with quote Quote
Permalink 0 Nadeem Iqbal
Thanks a lot for sharing a much needed programming code.

Can you please tell me how to change the code so that only the last filled/data row be processed in this same way?
2016-04-01 03:28 Reply Reply with quote Quote
Permalink 0 Jeremiah
Is there a way to split the data but keep the formulas that are attached to the cells? is there a pasteformula that can be inserted in there somewhere?
2016-04-27 12:16 Reply Reply with quote Quote
Permalink +1 step
How to do if the number of column that I want to split the data based on is more than 1?
2016-05-23 12:09 Reply Reply with quote Quote
Permalink 0 Aaron
Thanks for the code, but is only seems to work for some of the data. There are big gaps where Sheet1-Sheet5 are blank, but Sheet6 pulled the information as it was supposed to. Any guidance on how to correct this would be greatly appreciated. Thanks!
2016-05-25 20:47 Reply Reply with quote Quote
Permalink +1 RUDY
Is there a way to get this code to transfer formulas that may be in the sheet to the tabs?
2016-06-15 14:01 Reply Reply with quote Quote
Permalink +1 Stratos
Hello everybody,

I have a question. Everything runs perfectly (but slowly), but there is a problem.
I want to make the first 3 rows as headers in each tab. Just like the first one.

The data begin from row 4 till the end. So, how can I edit the code to get the first 3 rows not only the first one?

Thank you
2016-06-17 09:45 Reply Reply with quote Quote
Permalink 0 Liam
Hi all,

I have a header from rows 1 to 14 that I want to carry over to each separated sheet (my title row is row 14). How do I alter the code above to achieve this?

Thanks in advance!
2016-06-21 17:14 Reply Reply with quote Quote
Permalink 0 Steven
This code appears to be matching what I need, however, there are two issues that I am running into. First, my "Sheet1" includes the names of each respective worksheets in column A and is adding that additional column to each of the sheets. Which is not how they are currently formatted. Is there a way to change what is being transferred? In my case the data I need from "Sheet1" is in columns B-H and then need to be in columns A-G for each of the respective sheets. Second issue, how can I insert this data rather than writing over existing data? If these two items can be answered, this would be a game changer for me. Thanks!
2016-06-23 21:24 Reply Reply with quote Quote
Permalink 0 Loren Kumar
this has been great help for me.
is there a way to do the opposite?
I have multiple worksheet with different names, I want to create a master sheet with data from each of the worksheet
thanks
2016-07-03 23:42 Reply Reply with quote Quote
Permalink 0 Rahul
Above code work fine.

I want specify which column should pasted to now sheet like A-I columns only.

I cant use cell function in above code.

Can some guide me to solve above error.

where i need to make change in code.
2016-07-19 12:29 Reply Reply with quote Quote
Permalink 0 Swapnil
Hi Guys please help i have use below VBA code for Automatically split rows into different workbook but i have get error of Run -Time error 1004 (ActiveSheet.Co lumns.AutoFilte r Field:=Columns( vColumn).Column , Criterial:=vfil ter) .

Sub split()
Dim wswb As String
Dim wssh As String

wswb = ActiveWorkbook.Name
wssh = ActiveSheet.Name

vColumn = InputBox("pleas e indicate which column (i.e. A,B,C,...), you would like to split by", "column selection")

Columns(vColumn).Copy
Sheets.Add
ActiveSheet.Name = "_Summary"
Range("A1").Pas teSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes

vCounter = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To vCounter
vfilter = Sheets("_Summar y").Cells(i, 1)
Sheets(wssh).Ac tivate
ActiveSheet.Col umns.AutoFilter Field:=Columns( vColumn).Column , Criterial:=vfil ter
Cells.Copy
Workbooks.Add
Range("A1").Pas teSpecial
If vfilter "" Then
ActiveWorkbook. SaveAs ThisWorkbook.Pa th & "\Split Results\" & vfilter
Else
ActiveWorkbook. SaveAs ThisWorkbook.Pa th & "\Split Results\_empty"
End If

ActiveWorkbook.Close
Workbooks(wswb).Activate
Next i

Sheets("_Summary").Delete


End Sub
2016-07-27 12:10 Reply Reply with quote Quote
Permalink 0 julius D
thanks you very much for your support
the Quote:
code work well
2016-07-28 10:10 Reply Reply with quote Quote
Permalink 0 Sneha
Hey,
thanks it worked for me.. It was a magic...

My only requirement is on the other sheets, I want the Data to be displayed from A100 and not from first first cell i.e. A1.. Please can you tell the feild to be changed for it...

Thank you
2016-07-29 10:53 Reply Reply with quote Quote
Permalink +1 Vy nguyen
I found the VBA super helpful for my problem.

However, when I need to modify it, I am not sure how.

The column I need to have the split is the 11th column, which I was able to modify.

However, it is not splitting correctly because of the format of this column. The formatting needs to stay this way – customized. I was able to get the tabs to split based on the information in the column, but no data shows.

currently the column format is 082816 (custom in excel), which means 08/28/16). I believe this is due to the code: Dim vcol As Integer. These are not integers so it is not finding anything hence no data is copied?

How do i modify the 'dim vcol as integer' part so that it works with my formatting?
Thank you
2016-08-04 21:39 Reply Reply with quote Quote
Permalink 0 Rohit
Hi,

Though the sheets are getting split, the data is reflecting only in the first tab(for the first row). Rest all tabs are blank.
2016-08-05 07:43 Reply Reply with quote Quote
Permalink 0 Rohit
Hi,

It works fine with a little tweaks to the code. But the data is getting populated without the headers. The code that i am using is:

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 5
Set ws = Sheets("Readine ss Data")
lr = ws.Cells(ws.Row s.Count, vcol).End(xlUp).Row
title = "A1:AC1"
titlerow = ws.Range(title).Cells(44).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) "" And Application.Wor ksheetFunction. Match(ws.Cells( i, vcol), ws.Columns(icol ), 0) = 0 Then
ws.Cells(ws.Row s.Count, icol).End(xlUp) .Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myar r(i) & ""
If Not Evaluate("=ISRE F('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.C opy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub
2016-08-05 08:15 Reply Reply with quote Quote
Permalink 0 tickelmepinc
Hi All,

The above code works for me, but the column i need to split is a date column. My date format is (080516) and it must stay this way. Currently the code is not picking up this column becauase these values formatted as "custom" and the code line Dim vcol As Integer is not picking it up.

How do i change it so i can get it to filter on my column of custom formatting. Right now it splits the data into multiple worksheets, but not data is pulled. only the header information.

Thank you
2016-08-05 14:29 Reply Reply with quote Quote
Permalink 0 Natalie
When I used the VBA code for some reason it took some of the data and not all. It created blank tabs for the data it did not take. It would name the the tab sheet2 and no data would be present in those tabs. Any ideas on how to fix this?
2016-08-08 14:15 Reply Reply with quote Quote
Permalink 0 Ben
Hi Natalie,

This is because the data in the column you are sorting on is too long - so it doesnt fit in the name of the sheet.
In the VBA if an error occurs like this it just skips it!

Hope that helps?
2016-08-18 13:55 Reply Reply with quote Quote
Permalink 0 Dean
works brilliantly but I want to use a button with it as I have created a template that I reuse and just copy the data over.

Needed urgently
2016-08-12 11:51 Reply Reply with quote Quote
Permalink 0 Ben
Hi Dean,

This is Easy enough!

You will need to enable to Developer Tab, to do this, go to:
File - Options - Customise Ribbon - Then Tick the "Developer" box on the right hand list.
After this you can Click the tab along the top and hit "insert", choose your button and where to put it. A wizard will appear and ask for a Macro - hit "New" - this then opens up the VBA page and you can add it in :)

Now you can do this at the click of a button!
2016-08-18 14:02 Reply Reply with quote Quote
Permalink 0 MUHAMMAD JAVED
HI AM JAVED PLEASE HELP ME.

I HAVE A MASTER SHEET AND CONTAINING THE FOLLOWING COLUMNS- Code NTN Name- Type- DAB No.- DAB Date TAX- NOW I WANT TO SPLIT DATA ON THE BASIS OF NTN OR NAME AND SPLIT DATA SHOULD BE INCLUDE THE VALUES TOTAL.
2016-08-19 04:41 Reply Reply with quote Quote
Permalink 0 MUHAMMAD JAVED
HI ALL

The above code works for me, but the column i need to split is a Name column and name column split data into different work sheet but the main thing is all name work sheet not done and some sheet looks empty please help.
2016-08-19 04:43 Reply Reply with quote Quote
Permalink 0 Natalie Molina
Hi,

The code worked for me but I need to add a second filter. When I use the code, the new sheets that the code created are filtered from Column D. The problem is that almost all of the new sheets have subsections. So I need to filter by column D AND by column E and create more sheets than the current code allows.
2016-09-01 16:40 Reply Reply with quote Quote
Permalink 0 Jemert
The really works but wonder if is there a way to delete the filter for the first produced sheet?
2016-09-27 04:00 Reply Reply with quote Quote
Permalink 0 Yna
Hi Author,

The code worked fine for me, however, I am looking for a code that would work the reverse.

I have Log in and Log out time of agents in sheets for each agent, and I need to get the log in and log out of each agent per date into 1 single sheet for that date.

Scenario:

Data:
Sheet 1 - Contains Agent 1's log in and logout time for January 1 - 5
Sheet 2 - Contains Agent 2's log in and logout time for January 1 - 5
Sheet 3 - Contains Agent 3's log in and logout time for January 1 - 5
Sheet 4 - Contains Agent 4's log in and logout time for January 1 - 5

Need:
Sheet 5 - Need this sheet populated with Agent 1 - 5's log in and logout for January 1
Sheet 6 - Need this sheet populated with Agent 1 - 5's log in and logout for January 2
Sheet 7 - Need this sheet populated with Agent 1 - 5's log in and logout for January 3
Sheet 8 - Need this sheet populated with Agent 1 - 5's log in and logout for January 4
Sheet 9 - Need this sheet populated with Agent 1 - 5's log in and logout for January 5

Where: Sheet 5 - 9 column 1 is already populated with Agents names - and column 2 and 3 should be populated with log in and log out time (from sheet 1 - 4)


Hope you can help me please. Thank you very much!!
2016-09-27 08:06 Reply Reply with quote Quote
Permalink 0 Joel
Hi Author

Thank you for this, it works awesome. I am wondering if there is any way to use the Kutools feature, but have it split up the data within the same workbook? What I am trying to is continually add data to the main worksheet and then have each individuals table automatically updated as this data gets added. Thank you!
2016-10-20 20:30 Reply Reply with quote Quote
Permalink 0 skumar
Thank you for the code. works like a charm. this is what I am looking.

In addition to that can someone help me for below two things.

1. I want to apply autofilter and freeze top row(header) on all worksheets

2. In my requirement i have numbers in column A (1,2,3,4,5..til l 15) instead of names what we have here in example. I want to add/suffix alphabet to each number as "P". I don't want to add in my data but I want to add this specific letter alphabet "P" to all 15 worksheets tab(name)

Thank you in advance.
2016-10-21 15:42 Reply Reply with quote Quote
Permalink 0 RP
When I run the code, it splits into worksheets but data isn't copied. What do I do?
2016-10-24 06:06 Reply Reply with quote Quote
Permalink 0 RP
Hi

Thank you for the awesome tool. But though it splits into worksheets, there's no data in them.
Can you please help me out?

Thank you!
RP
2016-10-24 06:14 Reply Reply with quote Quote
Permalink 0 skumar
Thank you VBA code works like charm.

Can you please help me how to add these below features to existing code.

1. Apply Autofilter on all worksheets
2. Apply Freezepanes top row.(header) on all worksheets.
3. In my data I have numbers in column A (1,2,3,4,5..til l 15). I want to add/suffix alphabet to each number as "P". I don't want to add in my data but I want to add this specific letter alphabet "P" to all 15 worksheets tab(name of worksheet) (for example: P1,P2,P3,P4...P15)
4. Instead of calling this code as macro from excel, I want to call this code as batch script.

Thank you in advance.
2016-10-24 17:38 Reply Reply with quote Quote
Permalink 0 Felicia
You are an Excel Angel! This code worked perfectly. Thanks so much for your generosity with your knowledge!
2016-11-07 16:11 Reply Reply with quote Quote
Permalink 0 Tiago__
Hey Guys,

The code was awesome, thank you, but I have some problem. The Data I have is in the row 1 and 2, I tried to make a new column in vba with concatenate. However when I run the code, it interprets as nothing was there and only give me blanks despite the columns are filled.
2016-11-09 21:54 Reply Reply with quote Quote
Permalink 0 HG
OMG...

Best code in the world. Worked like a champ!
2016-11-21 21:59 Reply Reply with quote Quote
Permalink 0 SMP
Hi

The code worked great but I would like a variation, I need the code to only create specified worksheets for example:

If my main sheet is "All Outstanding" I would want the code to only copy the data to the following sheets eg. Avis, Coates Hire , Ultrafleet, Blackwoods, Westrac and Hitachi. Can you help. Thank You
2016-11-24 01:18 Reply Reply with quote Quote
Permalink 0 SHARON
Hi

Are you able to assist I have tried your code and it work great. My problem is when your code creates the new sheets I only need it to do this once for example the following sheets eg. Avis, Coates & Westrac and then for the data to be updated each time the code is run.

Thank you
2016-11-27 22:18 Reply Reply with quote Quote
Permalink 0 BILLY
Hi,

Very great system and after save I try to recall the module application but can't recall it
2016-12-27 01:45 Reply Reply with quote Quote
Permalink 0 KT
Hi, I need the same thing to be separated in different workbooks and not tabs. Will you be able to help me with code.

Look forward for a response.
2016-12-27 22:39 Reply Reply with quote Quote
Permalink 0 KT
Hi,

Can you help me with a formula to separate the data into multiple Workbooks than sheets?

Thanks
2016-12-29 15:26 Reply Reply with quote Quote
Permalink 0 Anita
The Code works great, unless the name you are splitting on is longer than 30 characters (max length of worksheet names). The code still ran without error, but the worksheets were blank and had a blank tab name where those data should have been. I solved this by truncating that column to the Left 30 characters and running the code again in a fresh worksheet, but just thought I'd put it out there.
2017-01-11 00:37 Reply Reply with quote Quote
Permalink 0 Mark
I thnk this VBA script is great! however we have a problem that the formulas are being stripped out of the data/ cells on the following tabs... how can I amend the script please to copy formulas for (in this case for hyperlinks)

I can see you have helped some users with the same issue above.

Cheers in advance :)
2017-01-12 12:31 Reply Reply with quote Quote
Permalink 0 ashok Kaundal
Thanks for Posting !
2017-02-02 05:34 Reply Reply with quote Quote
Permalink 0 Mark
I have found a problem, whilst I do have everything split into separate sheets now it seems to have included alot of white space to the right in each new workbook - not only that the source sheet is now bloated with white space also - have a look at the size of the horizontal scroll bar on the source sheet before and after running this VBS.
This has increased the size of my xlsx file. I need to find an easy way to remove the white space bloat now.
2017-02-06 03:07 Reply Reply with quote Quote
Permalink 0 Mark
I have successfully split my source sheet into multiple sheets, but I have found a problem.
This introduces alot of white space into the sheets. Have a look at the horizontal scroll bar before and after running the VBS.
I only had columns to Y, now the sheets (the source and new sheets) go to XFD! How do I delete the white space!?
Thanks otherwise for the code.
2017-02-06 03:11 Reply Reply with quote Quote
Permalink 0 Rohit
Awesome !
Thank you so much for publishing this code. It is immensely helpful for learners like me who study other's code to get better at VBA
2017-02-06 13:31 Reply Reply with quote Quote
Permalink 0 Mark
Using this script has increased the size of my file! It has inserted white space - how do I get rid of that?
2017-02-07 05:09 Reply Reply with quote Quote
Permalink 0 Lubo
Hello,
The VBA works great,
Do you give me more information how to chance to separated data in different sheet started from number row 36 and fill only 3- 5 columns
Thank you in advance
2017-02-07 10:59 Reply Reply with quote Quote
Permalink 0 Vikas
Hi
i am not getting correct result, number sheets are correct but getting data in one sheet only all other sheets are blank. can anybody help me please
2017-02-16 09:57 Reply Reply with quote Quote
Permalink 0 Alicia Jessup
I'm getting a run time error '1004'
Application-defined or object-defined error

when I debug it points out:
lr = ws.Cells(ws.Row s.Count, vcol).End(xlUp).Row

how do I make this work?
2017-02-23 15:56 Reply Reply with quote Quote
Permalink 0 suresh
unable to split the data popping out error as "Run-time error "6"" over flow.. Could you help me on this
2017-02-27 14:11 Reply Reply with quote Quote
Permalink 0 suresh
I am not getting the result. Popping out as Run time error '6' over flow
2017-02-27 14:14 Reply Reply with quote Quote
Permalink 0 riFph
This makes one sheet for every row making multiple sheets with the same name. Also it copies all the data not just the entries matching the sheet name.

Anyone help?
2017-02-28 22:21 Reply Reply with quote Quote
Permalink 0 Tiffany
I have two workbooks that already have information in them. Each workbook has sheets with the same name (1003 for example). The first workbook has scores by quarter. The second workbook is a review of the year with a lot more information in it. How can I write a code that would pull values from the QUARTERS book and link them into the YEAR book without having to do it manually? I'm envisioning that the name of the sheet will be the common denominator.

Thanks in advance!
2017-03-02 15:26 Reply Reply with quote Quote
Permalink 0 D C
VBA code is very good .It saves a lot of time thank your for this code thank you very much........... ............... ............... ...............
2017-03-05 08:04 Reply Reply with quote Quote
Permalink 0 RC-S
You are quite frankly amazing. This macro saved my bacon in more ways than one :lol:
2017-03-09 22:18 Reply Reply with quote Quote
Permalink 0 Anshul Gupta
Hi, My files doesn't have titles, how can i tweak the code such that it won't contain first row as title
2017-03-23 06:32 Reply Reply with quote Quote
Permalink 0 Shwetha
Thank you for the code. It works good. It saved me lot of time from recreating the whole thing.
2017-04-12 23:32 Reply Reply with quote Quote
Permalink 0 RiggaToni
Thank you! Saved me a ton of time!
2017-04-18 13:34 Reply Reply with quote Quote
Permalink 0 Christopher
This code worked great! Very useful...

On only had one error... One of the groupings that I split was titled "MAINTENANCE/OP ERATIONS" - as we all know, Excel doesn't like to title sheet names with "/" in the title. The script you offered created a blank sheet (Sheet 24) is what it titled it, but it failed to copy the header and data from the original sheet. Not a big deal, I was able to copy and paste this single instance to the sheet and rename the sheet "MAINTENANCE-OP ERATIONS" to comply with Excel's rules about naming sheets.

Is there a way to revise the code so that when I run the module, it will automatically change bad characters in a sheet name to a default character like "_". I plan on using this code a lot and I am certain that the data sheets I will be running will have this issue.

Thank you so much for any help!
2017-04-21 20:00 Reply Reply with quote Quote
Permalink 0 Chuck
This code will probably save me a few hours over the next few months! Thanks!!!

Is there a way to then open all of those new tabs in separate windows? Or, is there a way to email a single tab to someone and not the entire thing?

Thanks again!
2017-05-04 18:49 Reply Reply with quote Quote
Permalink 0 suly
how to extract each letter of the date in sheet1 < sheet2 got some dates> and make it apear in sheet3 for example

sheet1
james 22.4.2017 23.4.2017 24.4.2017
a r r

tom 22.4.2017 23.4.2017 24.4.2017
r r r


sheet2

james 22.4.2017

james 23.4.2017

tom 24.4.2017

sheet3


james 22.4.2017 a

james 23.4.2017 r

tom 24.4.2017 r





and thank u
2017-05-22 10:34 Reply Reply with quote Quote
Permalink 0 Kate
This is amazeballs ! thanks - I have been asking how to do this with the whizz kids and its so complicated. Really pleased with it
2017-05-22 14:15 Reply Reply with quote Quote

Add comment


Security code
Refresh