great work SAF Business Analytics. Actually this lesson has helped me sort over 40,000 rows of data in excel for credit recovery report for deliquent loans. the report was to look at deliquency per product and per employer or company from where several employees have taken credit. I even visited your linkedin profile and made you a connection. Thanks again
Thnx for showing how to split the data into multiple workbooks. Like you make workbooks as per division, suppose if I need to send this workbook to each divisional supervisor via outlook then what will be code & same for Gmail.
@@laurasmith740 @SAF Business Analytics Hi, my name is sumit... I need your small help. I am looking for this kind of macro from a long time but need little advice. Can it be possible that we can add one more sheet at same time containing on master file and after that same as shown in video. Please advise!!! Just to clear i mean 1 extra sheet containing a summary of splitted file. Please help...
Thank you for the lesson. Loved it! If the name of the file that we save has a special character, it will not be saved. If we remove the special character we will not be able to filter. Here in the code we have given the file name as vFilter, but i want everything to be the same and only the file name in a format that we can save i.e ( does not include special characters).How can i do that? i removed all the special characters and pasted the names in coulmn B, but unable to use two loops. please help!
Hopefully you are still around, but my macro keeps failing at the AutoFilter section. I'm getting the "AutoFilter method of Range class failed" Run-Time Error '1004': No clue what's going on here, I was hoping you could elaborate on what that particular line is doing and if you had any thoughts on what might be failing for me.
Hi, i love your video. Just need small help. I need one more workbook to be added automatically to all the sheets. Is that possible. Actually that workbook contain pivot related to the data that got split. PLease help
Im having trouble with the below line of code: Run-time error '1004': AutoFilter method of Range class failed ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter not sure what I need to update? Please help.
Hello, Can you help me with a problem that I have after following your tutorial? Everything works but all the new files have 5mb, while I started from a file of 24kb and every file has just a row. What could be the reason?
Is it possible to split into multiple tabs within the same workbook, based on a range of numerical values in one column? Example: All rows with values between 270xxxxxx to 279xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLA___. All rows with values between 300xxxxxx to 329xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLB___. All rows with values between 360xxxxxx to 369xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLC___. ..so on and so forth.
Hi, I've tried this code and it's work for my job. But, there are two problems that happened to me: 1. after all new workbook are generated, then the workbook which is open/active is the last new splitted workbook that is generated (not the master workbook). Can I make the master workbook to keep active and open even if the new splitted woorkbook is generated? 2. Because I need to do it everyday (because the new master workbook will also be updated everyday), so I need to overwrite the splitted workbook which is generated yesterday with the new workbook that will be generated today. When I use this code, I can not overwrite the last splitted workbook with the new splitted workbook. Could you please help me with this? Thankyou in advance
I also got Run-time error '1004' AutoFilter method of Range class Failed. Hi same issue as others. Code worked great up to line ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter, Any solution on this
+SAF Business Analytics - Hi, I am hoping you receive this message. First, thank you so much for posting this it's wonderful I was able to execute it without issues. I just have one minor set back that I am hoping you could help me with. Instead of the information pasting into the code you wrote "Sheets.Add" then "ActiveSheet.Name = "_Summary" " is it possible for me to paste it into a preformatted template instead? If so, what would the coding be for that? Any help would be greatly appreciated! Regards,Jamilka
Hello Sir, I want to automate a process using vba script. We have around 1lac records of employees. Need to filter by employee name and export to. Txt file with pipe delimeter. Can you help me on this!
Hi, some output files that were save were not in the xlsx or any excel related format. I had to add a .xlsx to open them. what could be the issue. Most were ok and results as expected except for the format of some of the data files. all data files were over 240!
After a while studying the code line by line and output work books, i realized a very small but seeming important clue as to why some of the files were not in any excel extension. The names that you filter with must not have full stops or dots. for instance abbreviation like London B.R. will of course filter out data for London B.R. and put in a new work book, rename it London B.R. BUT it will not be an excel file. You can manually (at this point) type .xlsx at end of this name and will change to excel readable format. Great learning
Hello Friend, I use the code below to separate and create other Workbooks, I use to do the following: I choose the column with a person's name, line 2 and ready, I have a workbooks for each column name I chose. But what I need is for the code to parse two sheets at the same time, and then do that split. Each new workbooks should have two sheets with information relating to a person's name. For example on sheet 1 and 2 I will have the same people, but with different information. I need a workbooks for each of these people, but with the two tabs. Can you help me by modifying the code below so I can get this result? I'd be very happy! ;) Public Sub SplitToFiles() ' MACRO SplitToFiles ' Last update: 2012-03-04 ' Author: mtone ' Version 1.1 ' Description: ' Loops through a specified column, and split each distinct values into a separate file by making a copy and deleting rows below and above ' ' Note: Values in the column should be unique or sorted. ' ' The following cells are ignored when delimiting sections: ' - blank cells, or containing spaces only ' - same value repeated ' - cells containing "total" ' ' Files are saved in a "Split" subfolder from the location of the source workbook, and named after the section name. Dim osh As Worksheet ' Original sheet Dim iRow As Long ' Cursors Dim iCol As Long Dim iFirstRow As Long ' Constant Dim iTotalRows As Long ' Constant Dim iStartRow As Long ' Section delimiters Dim iStopRow As Long Dim sSectionName As String ' Section name (and filename) Dim rCell As Range ' current cell Dim owb As Workbook ' Original workbook Dim sFilePath As String ' Constant Dim iCount As Integer ' # of documents created iCol = Application.InputBox("Enter the column number used for splitting", "Select column", 2, , , , , 1) iRow = Application.InputBox("Enter the starting row number (to skip header)", "Select row", 5, , , , , 1) iFirstRow = iRow Set osh = Application.ActiveSheet Set owb = Application.ActiveWorkbook iTotalRows = osh.UsedRange.Rows.Count sFilePath = Application.ActiveWorkbook.Path If Dir(sFilePath + "\Split", vbDirectory) = "" Then MkDir sFilePath + "\Split" End If 'Turn Off Screen Updating Events Application.EnableEvents = False Application.ScreenUpdating = False Do ' Get cell at cursor Set rCell = osh.Cells(iRow, iCol) sCell = Replace(rCell.Text, " ", "") If sCell = "" Or (rCell.Text = sSectionName And iStartRow 0) Or InStr(1, rCell.Text, "total", vbTextCompare) 0 Then ' Skip condition met Else ' Found new section If iStartRow = 0 Then ' StartRow delimiter not set, meaning beginning a new section sSectionName = rCell.Text iStartRow = iRow Else ' StartRow delimiter set, meaning we reached the end of a section iStopRow = iRow - 1 ' Pass variables to a separate sub to create and save the new worksheet CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat iCount = iCount + 1 ' Reset section delimiters iStartRow = 0 iStopRow = 0 ' Ready to continue loop iRow = iRow - 1 End If End If ' Continue until last row is reached If iRow < iTotalRows Then iRow = iRow + 1 Else ' Finished. Save the last section iStopRow = iRow CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat iCount = iCount + 1 ' Exit Exit Do End If Loop 'Turn On Screen Updating Events Application.ScreenUpdating = True Application.EnableEvents = True MsgBox Str(iCount) + " documents saved in " + sFilePath End Sub Public Sub DeleteRows(targetSheet As Worksheet, RowFrom As Long, RowTo As Long) Dim rngRange As Range Set rngRange = Range(targetSheet.Cells(RowFrom, 1), targetSheet.Cells(RowTo, 1)).EntireRow rngRange.Select rngRange.Delete End Sub Public Sub CopySheet(osh As Worksheet, iFirstRow As Long, iStartRow As Long, iStopRow As Long, iTotalRows As Long, sFilePath As String, sSectionName As String, fileFormat As XlFileFormat) Dim ash As Worksheet ' Copied sheet Dim awb As Workbook ' New workbook ' Copy book osh.Copy Set ash = Application.ActiveSheet ' Delete Rows after section If iTotalRows > iStopRow Then DeleteRows ash, iStopRow + 1, iTotalRows End If ' Delete Rows before section If iStartRow > iFirstRow Then DeleteRows ash, iFirstRow, iStartRow - 1 End If ' Select left-topmost cell ash.Cells(1, 1).Select ' Clean up a few characters to prevent invalid filename sSectionName = Replace(sSectionName, "/", " ") sSectionName = Replace(sSectionName, "\", " ") sSectionName = Replace(sSectionName, ":", " ") sSectionName = Replace(sSectionName, "=", " ") sSectionName = Replace(sSectionName, "*", " ") sSectionName = Replace(sSectionName, ".", " ") sSectionName = Replace(sSectionName, "?", " ") ' Save in same format as original workbook ash.SaveAs sFilePath + "\Split\" + sSectionName, fileFormat ' Close Set awb = ash.Parent awb.Close SaveChanges:=False End Sub
Hi, I tried with your code, and it did generate multiple workbooks. However, each workbook only has the variable names in the first row, and the rest is just blank. Do you know what the problem might be? Thanks!!!
I get an error message at one of the steps. After the Summary tab is created, the code pastes column A into the summary tab but the data promptly disappears (after one second) from the summary tab and this causes the next step to fail because there is no data. Why is that happening?
+Mututala I am glad we were able to get this resolved. Just remember to write your code in the module not the sheet (unless you want a specific action in a sheet).
Can u help me with error 1004 in the below Sub Split() Dim WSWB As String Dim WSSH As String WSWB = ActiveWorkbook.Name WSSH = ActiveSheet.Name vColumn = InputBox("Enter Column Name,Split By..", "Column Selection") Columns(vColumn).Copy Sheets.Add ActiveSheet.Name = "Summary" Range("A1").PasteSpecial Columns("A").RemoveDuplicates Columns:=1, hedder:=xlYes vCounter = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To vCounter vFilter = Sheets("Summary").Cells(1, 1) Sheets(WSSH).Active ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter Cells.Copy Workbooks.Add Range("A1").PasteSpecial If vFilter "" Then ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Result\" & vFilter Else ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Result\Empty" End If ActiveWorkbook.Close Workbooks(WSWB).Active Next i Sheets("Summary").Delete End Sub
+Mithun Hallur Here you go Sub Split() Dim wswb As String Dim wssh As String wswb = ActiveWorkbook.Name wssh = ActiveSheet.Name vColumn = InputBox("Please indicate which column you would like to split by", "Column selection") Columns(vColumn).Copy Sheets.Add ActiveSheet.Name = "_Summary" Range("A1").PasteSpecial Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes vCounter = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To vCounter vFilter = Sheets("_Summary").Cells(i, 1) Sheets(wssh).Activate ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter Cells.Copy Workbooks.Add Range("A1").PasteSpecial If vFilter "" Then ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split\" & vFilter Else ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split\_Empty" End If ActiveWorkbook.Close Workbooks(wswb).Activate Next i Sheets("_Summary").Delete End Sub
@@SAFBusinessAnalytics Hi, my name is sumit... I need your small help. I am looking for this kind of macro from a long time but need little advice. Can it be possible that we can add one more sheet at same time containing on master file and after that same as shown in video. Please advise!!! Just to clear i mean 1 extra sheet containing a summary of splitted file. Please help...
I need a macro for following condition suppose i have customer excel file in which first 7 rows is for header so, from 8th rowrecords are start i need to split rows of 500 record each in one file and save them with name customer1,customer2,customer3,........ suppose i have customer file of 2540 records so it split in customer1 which have header rows with record starts from 8th row to 507th row customer2 which have header rows with record starts from 508th row to 1007th row customer3 which have header rows with record starts from 1008th row to 1507th row customer4 which have header rows with record starts from 1508th row to 2007th row customer5 which have header rows with record starts from 2008th row to 2507th row customer6 which have header rows with record starts from 2508th row to 2540th row
I have an excel work book with 6 datasheets, now every data sheet has VARIABLE X and VARIABLE Y along with other data. VARIABLE X has values like "a", "b", "c" and VARIABLE Y has values like "e","f", "g". Now what i want is from this excel workbook i want to split the file into 6 sub files , first keeping only values from Variable X and then Variable Y.
Run-time error '1004' AutoFilter method of Range class Failed. Hi same issue as others. Code worked great up to line ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter Tried all options does not seem to change this error. Any ideas would be welcome. Great code though! Duncan
Here is my requirement, I want to break a huge excel file containing some 1000k rows to a standard ~60k row line items for each file with a custom rule or just copy 60K row lines and create files till the end of the rows will be completed
I get an error message at one of the steps. After the Summary tab is created, the code pastes column A into the summary tab but the data promptly disappears (after one second) from the summary tab and this causes the next step to fail because there is no data. Why is that happening?
great work SAF Business Analytics. Actually this lesson has helped me sort over 40,000 rows of data in excel for credit recovery report for deliquent loans. the report was to look at deliquency per product and per employer or company from where several employees have taken credit.
I even visited your linkedin profile and made you a connection.
Thanks again
Thank you! Thank you! I had a spreadsheet of 41000 lines that I needed to break into 1000 spreadsheets. This was awesome!
can you please give me the coding for 1000 lines split?
Thank you very much bro. You helped me a lot, I was scractching my head to do this job but you made it very simple. Thank you again 😂😂😂
Terrific video, thank you! How do you maintain the original formatting in the newly created sheets?
Thanks For Video. How can we populate each file to outlook as separate email, while data is spliting.
Thanks for your code, the instruction is very clear. Appreciate it!
Thnx for showing how to split the data into multiple workbooks.
Like you make workbooks as per division, suppose if I need to send this workbook to each divisional supervisor via outlook then what will be code & same for Gmail.
Did you figure this one out? It is exactly what I need to do?
@@laurasmith740 @SAF Business Analytics Hi, my name is sumit... I need your small help. I am looking for this kind of macro from a long time but need little advice. Can it be possible that we can add one more sheet at same time containing on master file and after that same as shown in video. Please advise!!! Just to clear i mean 1 extra sheet containing a summary of splitted file. Please help...
Thank you for the lesson. Loved it! If the name of the file that we save has a special character, it will not be saved. If we remove the special character we will not be able to filter. Here in the code we have given the file name as vFilter, but i want everything to be the same and only the file name in a format that we can save i.e ( does not include special characters).How can i do that? i removed all the special characters and pasted the names in coulmn B, but unable to use two loops. please help!
how do you make sure you keep the first row accross sheets? the headings"
Hopefully you are still around, but my macro keeps failing at the AutoFilter section. I'm getting the "AutoFilter method of Range class failed" Run-Time Error '1004': No clue what's going on here, I was hoping you could elaborate on what that particular line is doing and if you had any thoughts on what might be failing for me.
Hi, i love your video. Just need small help. I need one more workbook to be added automatically to all the sheets. Is that possible. Actually that workbook contain pivot related to the data that got split. PLease help
can you suggest how we can separate the data in the same workbook with multiple sheets
Thanks for uploading the video.. 🙏🙏
Please share the VBA codes
Great explanation. It really helped me.
Hi,Thanks for the excellent step by step explanation. Do the VBA code is saved anywhere, I need to use it. Thanks in advance.
Hi not too sure are you still answering ques here? have some ques in mind, thanks
Hi,
This code is not working for columns which contains number. Please help
Im having trouble with the below line of code: Run-time error '1004':
AutoFilter method of Range class failed
ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
not sure what I need to update? Please help.
Hello,
Can you help me with a problem that I have after following your tutorial?
Everything works but all the new files have 5mb, while I started from a file of 24kb and every file has just a row.
What could be the reason?
Is it possible to split into multiple tabs within the same workbook, based on a range of numerical values in one column?
Example: All rows with values between 270xxxxxx to 279xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLA___.
All rows with values between 300xxxxxx to 329xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLB___.
All rows with values between 360xxxxxx to 369xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLC___.
..so on and so forth.
Hi, I've tried this code and it's work for my job. But, there are two problems that happened to me:
1. after all new workbook are generated, then the workbook which is open/active is the last new splitted workbook that is generated (not the master workbook).
Can I make the master workbook to keep active and open even if the new splitted woorkbook is generated?
2. Because I need to do it everyday (because the new master workbook will also be updated everyday), so I need to overwrite the splitted workbook which is generated yesterday with the new workbook that will be generated today. When I use this code, I can not overwrite the last splitted workbook with the new splitted workbook.
Could you please help me with this? Thankyou in advance
I also got Run-time error '1004' AutoFilter method of Range class Failed. Hi same issue as others. Code worked great up to line ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter, Any solution on this
I need special help on this tutorial. How can I reach you
+SAF Business Analytics - Hi, I am hoping you receive this message. First, thank you so much for posting this it's wonderful I was able to execute it without issues. I just have one minor set back that I am hoping you could help me with. Instead of the information pasting into the code you wrote "Sheets.Add" then "ActiveSheet.Name = "_Summary" " is it possible for me to paste it into a preformatted template instead? If so, what would the coding be for that? Any help would be greatly appreciated! Regards,Jamilka
Hello Sir, I want to automate a process using vba script. We have around 1lac records of employees. Need to filter by employee name and export to. Txt file with pipe delimeter. Can you help me on this!
This is great script but i would like to know how to modify to just create tab's in same workbook and rename.
Can you please provide us the code to copy it directly
HI, iam getting error "ActiveSheet.Columns.AutoFilter field:=Columns(vcolumn).Column, criterial:=vfilter" pls help me out to sort this pls........
Dear Sir
my workbook not saving stopped with bellow code please help me
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split\" & vFilter
Hi, some output files that were save were not in the xlsx or any excel related format. I had to add a .xlsx to open them. what could be the issue.
Most were ok and results as expected except for the format of some of the data files. all data files were over 240!
Hi Eric - could you send me an example workbook and I could investigate?
After a while studying the code line by line and output work books, i realized a very small but seeming important clue as to why some of the files were not in any excel extension.
The names that you filter with must not have full stops or dots. for instance abbreviation like London B.R. will of course filter out data for London B.R. and put in a new work book, rename it London B.R. BUT it will not be an excel file. You can manually (at this point) type .xlsx at end of this name and will change to excel readable format.
Great learning
Great find! You can probably use replace to change the file name at the end. Happy learnings!
Hello Friend,
I use the code below to separate and create other Workbooks, I use to do the following: I choose the column with a person's name, line 2 and ready, I have a workbooks for each column name I chose.
But what I need is for the code to parse two sheets at the same time, and then do that split. Each new workbooks should have two sheets with information relating to a person's name. For example on sheet 1 and 2 I will have the same people, but with different information. I need a workbooks for each of these people, but with the two tabs.
Can you help me by modifying the code below so I can get this result?
I'd be very happy! ;)
Public Sub SplitToFiles()
' MACRO SplitToFiles
' Last update: 2012-03-04
' Author: mtone
' Version 1.1
' Description:
' Loops through a specified column, and split each distinct values into a separate file by making a copy and deleting rows below and above
'
' Note: Values in the column should be unique or sorted.
'
' The following cells are ignored when delimiting sections:
' - blank cells, or containing spaces only
' - same value repeated
' - cells containing "total"
'
' Files are saved in a "Split" subfolder from the location of the source workbook, and named after the section name.
Dim osh As Worksheet ' Original sheet
Dim iRow As Long ' Cursors
Dim iCol As Long
Dim iFirstRow As Long ' Constant
Dim iTotalRows As Long ' Constant
Dim iStartRow As Long ' Section delimiters
Dim iStopRow As Long
Dim sSectionName As String ' Section name (and filename)
Dim rCell As Range ' current cell
Dim owb As Workbook ' Original workbook
Dim sFilePath As String ' Constant
Dim iCount As Integer ' # of documents created
iCol = Application.InputBox("Enter the column number used for splitting", "Select column", 2, , , , , 1)
iRow = Application.InputBox("Enter the starting row number (to skip header)", "Select row", 5, , , , , 1)
iFirstRow = iRow
Set osh = Application.ActiveSheet
Set owb = Application.ActiveWorkbook
iTotalRows = osh.UsedRange.Rows.Count
sFilePath = Application.ActiveWorkbook.Path
If Dir(sFilePath + "\Split", vbDirectory) = "" Then
MkDir sFilePath + "\Split"
End If
'Turn Off Screen Updating Events
Application.EnableEvents = False
Application.ScreenUpdating = False
Do
' Get cell at cursor
Set rCell = osh.Cells(iRow, iCol)
sCell = Replace(rCell.Text, " ", "")
If sCell = "" Or (rCell.Text = sSectionName And iStartRow 0) Or InStr(1, rCell.Text, "total", vbTextCompare) 0 Then
' Skip condition met
Else
' Found new section
If iStartRow = 0 Then
' StartRow delimiter not set, meaning beginning a new section
sSectionName = rCell.Text
iStartRow = iRow
Else
' StartRow delimiter set, meaning we reached the end of a section
iStopRow = iRow - 1
' Pass variables to a separate sub to create and save the new worksheet
CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat
iCount = iCount + 1
' Reset section delimiters
iStartRow = 0
iStopRow = 0
' Ready to continue loop
iRow = iRow - 1
End If
End If
' Continue until last row is reached
If iRow < iTotalRows Then
iRow = iRow + 1
Else
' Finished. Save the last section
iStopRow = iRow
CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat
iCount = iCount + 1
' Exit
Exit Do
End If
Loop
'Turn On Screen Updating Events
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox Str(iCount) + " documents saved in " + sFilePath
End Sub
Public Sub DeleteRows(targetSheet As Worksheet, RowFrom As Long, RowTo As Long)
Dim rngRange As Range
Set rngRange = Range(targetSheet.Cells(RowFrom, 1), targetSheet.Cells(RowTo, 1)).EntireRow
rngRange.Select
rngRange.Delete
End Sub
Public Sub CopySheet(osh As Worksheet, iFirstRow As Long, iStartRow As Long, iStopRow As Long, iTotalRows As Long, sFilePath As String, sSectionName As String, fileFormat As XlFileFormat)
Dim ash As Worksheet ' Copied sheet
Dim awb As Workbook ' New workbook
' Copy book
osh.Copy
Set ash = Application.ActiveSheet
' Delete Rows after section
If iTotalRows > iStopRow Then
DeleteRows ash, iStopRow + 1, iTotalRows
End If
' Delete Rows before section
If iStartRow > iFirstRow Then
DeleteRows ash, iFirstRow, iStartRow - 1
End If
' Select left-topmost cell
ash.Cells(1, 1).Select
' Clean up a few characters to prevent invalid filename
sSectionName = Replace(sSectionName, "/", " ")
sSectionName = Replace(sSectionName, "\", " ")
sSectionName = Replace(sSectionName, ":", " ")
sSectionName = Replace(sSectionName, "=", " ")
sSectionName = Replace(sSectionName, "*", " ")
sSectionName = Replace(sSectionName, ".", " ")
sSectionName = Replace(sSectionName, "?", " ")
' Save in same format as original workbook
ash.SaveAs sFilePath + "\Split\" + sSectionName, fileFormat
' Close
Set awb = ash.Parent
awb.Close SaveChanges:=False
End Sub
Getting error like vcolumn variable not defined
please, we need this VBA Code
Hi, I tried with your code, and it did generate multiple workbooks. However, each workbook only has the variable names in the first row, and the rest is just blank. Do you know what the problem might be? Thanks!!!
Do you want to send me a sample of your workbook?
Just sent :) Thank you so much!!!
I get an error message at one of the steps. After the Summary tab is created, the code pastes column A into the summary tab but the data promptly disappears (after one second) from the summary tab and this causes the next step to fail because there is no data. Why is that happening?
+Mututala Are you able to send me the workbook? My email is in the about section of my account. Thanks.
+SAF Business Analytics I just emailed you. Thank you.
+Mututala I am glad we were able to get this resolved. Just remember to write your code in the module not the sheet (unless you want a specific action in a sheet).
Can u help me with error 1004 in the below
Sub Split()
Dim WSWB As String
Dim WSSH As String
WSWB = ActiveWorkbook.Name
WSSH = ActiveSheet.Name
vColumn = InputBox("Enter Column Name,Split By..", "Column Selection")
Columns(vColumn).Copy
Sheets.Add
ActiveSheet.Name = "Summary"
Range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, hedder:=xlYes
vCounter = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To vCounter
vFilter = Sheets("Summary").Cells(1, 1)
Sheets(WSSH).Active
ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter
Cells.Copy
Workbooks.Add
Range("A1").PasteSpecial
If vFilter "" Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Result\" & vFilter
Else
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Result\Empty"
End If
ActiveWorkbook.Close
Workbooks(WSWB).Active
Next i
Sheets("Summary").Delete
End Sub
Can you post the code
Great Video!! Thanks a lot.
Where do i get code for this video.
+Mithun Hallur Here you go
Sub Split()
Dim wswb As String
Dim wssh As String
wswb = ActiveWorkbook.Name
wssh = ActiveSheet.Name
vColumn = InputBox("Please indicate which column you would like to split by", "Column selection")
Columns(vColumn).Copy
Sheets.Add
ActiveSheet.Name = "_Summary"
Range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
vCounter = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To vCounter
vFilter = Sheets("_Summary").Cells(i, 1)
Sheets(wssh).Activate
ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter
Cells.Copy
Workbooks.Add
Range("A1").PasteSpecial
If vFilter "" Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split\" & vFilter
Else
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split\_Empty"
End If
ActiveWorkbook.Close
Workbooks(wswb).Activate
Next i
Sheets("_Summary").Delete
End Sub
Thank you SAF Business Analytics, this code saved an entire day's work for me!! You rock \../
I'm glad I could help!
@@SAFBusinessAnalytics I got problem on saving documents error as method save as of object _workbook failed please suggest
@@SAFBusinessAnalytics Hi, my name is sumit... I need your small help. I am looking for this kind of macro from a long time but need little advice. Can it be possible that we can add one more sheet at same time containing on master file and after that same as shown in video. Please advise!!! Just to clear i mean 1 extra sheet containing a summary of splitted file. Please help...
Great video, thank you so much
👍 excellent
Has anyone found a solution for the Run-time error 1004? I can't get pass the AutoFilter either.
Disregard! It works like a CHARM!
THANK YOU!!!
How u got the solution for Run-time error 1004 ?
I need a macro for following condition
suppose i have customer excel file in which first 7 rows is for header so, from 8th rowrecords are start
i need to split rows of 500 record each in one file and save them with name customer1,customer2,customer3,........
suppose i have customer file of 2540 records so it split in
customer1 which have header rows with record starts from 8th row to 507th row
customer2 which have header rows with record starts from 508th row to 1007th row
customer3 which have header rows with record starts from 1008th row to 1507th row
customer4 which have header rows with record starts from 1508th row to 2007th row
customer5 which have header rows with record starts from 2008th row to 2507th row
customer6 which have header rows with record starts from 2508th row to 2540th row
I have an excel work book with 6 datasheets, now every data sheet has VARIABLE X and VARIABLE Y along with other data. VARIABLE X has values like "a", "b", "c" and VARIABLE Y has values like "e","f", "g". Now what i want is from this excel workbook i want to split the file into 6 sub files , first keeping only values from Variable X and then Variable Y.
Thank you so much, amazing!
Run-time error '1004' AutoFilter method of Range class Failed. Hi same issue as others. Code worked great up to line ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter
Tried all options does not seem to change this error. Any ideas would be welcome.
Great code though!
Duncan
I know this is old, but did you try "criteria1" instead of "criterial"
i think it's not "criterial" it's "criteria1" it's a number not a letter. I had to trouble shoot this myself too lol.
Me also get same error any one can help out
Here is my requirement, I want to break a huge excel file containing some 1000k rows to a standard ~60k row line items for each file with a custom rule or just copy 60K row lines and create files till the end of the rows will be completed
The title is misleading. It is splitting by columns not by number of rows.
I get an error message at one of the steps. After the Summary tab is created, the code pastes column A into the summary tab but the data promptly disappears (after one second) from the summary tab and this causes the next step to fail because there is no data. Why is that happening?