Ted Penner
asked on
Separate files macro - Errors on larger data set
I had this question after viewing Debug file separation.
I have run the attached macro to produce a file separation based on unique values in the filename column.
The code seems to be failing on an actual data set and below it is a screenshot of those errors.
On the test file, which does not include actual data, the files created appear to have exactly the same data, which should not be occurring.
Please advise further.
Assistance are greatly appreciated.
I have run the attached macro to produce a file separation based on unique values in the filename column.
The code seems to be failing on an actual data set and below it is a screenshot of those errors.
On the test file, which does not include actual data, the files created appear to have exactly the same data, which should not be occurring.
Please advise further.
Assistance are greatly appreciated.
Sub Separate_by_Filename_Column_Into_Separate_Worksheet_Tabs()
Set origSh = ActiveSheet
Set rngFound = Nothing
Set rngFound = Range("1:1").Find("Filename")
If Not rngFound Is Nothing Then
strCol = Split(rngFound.Address, "$")(1)
For Each C In Range(Range(strCol & "2"), Range(strCol & Rows.Count).End(xlUp))
If Not Evaluate("=ISREF('" & C & "'!A1)") Then
origSh.UsedRange.AutoFilter Field:=rngFound.Column, Criteria1:=C.Value, VisibleDropDown:=True
ActiveWorkbook.Sheets.Add after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = C
origSh.UsedRange.Copy
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
origSh.Range(origSh.Range("A1"), origSh.Range("A1").SpecialCells(xlCellTypeLastCell)).EntireRow.Copy ActiveSheet.Range("A1")
ActiveSheet.UsedRange.AutoFilter
origSh.Activate
End If
Next
On Error Resume Next
ActiveSheet.UsedRange.AutoFilter
ActiveSheet.ShowAllData
On Error GoTo 0
End If
End Sub
Sub Separate_by_Filename_Column_Into_Separate_Workbook_Files()
Dim aFiles As Variant
strDirectory = GetFolder(ActiveWorkbook.Path)
If strDirectory = "" Then Exit Sub
Set origSh = ActiveSheet
origSh.UsedRange.ClearFormats
origSh.Range("A1").Select
Selection.CurrentRegion.Select
On Error Resume Next
origSh.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight16"
On Error GoTo 0
Set rngFound = Nothing
Set rngFound = Range("1:1").Find("Filename")
If Not rngFound Is Nothing Then
strCol = Split(rngFound.Address, "$")(1)
Set myRange = Range(Range(strCol & "2"), Range(strCol & Rows.Count).End(xlUp))
ActiveWorkbook.Sheets.Add
myRange.Copy ActiveSheet.Range("A1")
Application.DisplayAlerts = False
Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlNo
aFiles = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ActiveSheet.Delete
Application.DisplayAlerts = True
For Each C In aFiles
origSh.Copy
Set newWbk = ActiveWorkbook
Set newSh = newWbk.ActiveSheet
Set myNewRange = Range(Range(strCol & "1"), Range(strCol & Rows.Count).End(xlUp))
myNewRange.AutoFilter Field:=rngFound.Column, Criteria1:="<>" & C, VisibleDropDown:=True
Set origRng = Nothing
On Error Resume Next
Set origRng = newSh.Range(Range(strCol & "2"), Range(strCol & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0
ActiveSheet.ShowAllData
newWbk.SaveAs strDirectory & "\" & C
newWbk.Close False
Next
End If
End Sub
Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
160408-To-Be-Separated-EE-testing.xlsb.
ASKER
I wish I knew the answer. It was re-combined using the combiner I have, then scrubbed prior to sending it to you.
I need something that will scrub data also and that question has been posted here https://www.experts-exchange.com/questions/28936969/Fast-data-scrub.html
If I could get a good enough scrubbing tool, I could make my examples more closely resemble the structure of the data I am trying to process.
I need something that will scrub data also and that question has been posted here https://www.experts-exchange.com/questions/28936969/Fast-data-scrub.html
If I could get a good enough scrubbing tool, I could make my examples more closely resemble the structure of the data I am trying to process.
That thread looks like you want to use a macro to mask sensitive data. I doubt if it would actually help with the issue we have. It's not caused by the data itself, it's the volume of data. Just because Excel has increased the number of rows and columns it doesn't really mean Excel can cope with huge amounts of data.
ASKER
Understood. What I am hoping is that someone has found some approaches to dealing with this well known problem of Excel.
Would it not be easier to import the sheets from each workbook into it's own sheet within the master workbook?
I don't think you will find a solution to the not responding, it is just that Excel cannot cope with that amount of data.
I don't think you will find a solution to the not responding, it is just that Excel cannot cope with that amount of data.
ASKER
No, for this purpose it needs to be able to do files or tabs. The files process is not even working right
So tell me the steps that you need to do to achieve the end result.
ASKER
1 Click macro and choose either workbook files or worksheet tabs.
2 If file is selected, then the files will drop into the selected folder and put me back where I was.
2 If file is selected, then the files will drop into the selected folder and put me back where I was.
I want to know each step of the process so that I can look at it without trying to amend other people's code.
ASKER
The goal of it is to separate each of the rows for each file name as noted in that column, back into separate files, such that all of the files combined equal the 200,000+ rows that we started with. Each file name will have a different number of rows.
The result should not re-sort any of the data in the original or in the separated files, and the order or number of columns, or names of columns should not matter to the macro except with respect to the "Filename" column. The file names will take on whatever name for that row is noted in the file name column, regardless of whether that column is column A or another one.
The code should be able to make either worksheet tabs based on the "Filename" column, or workbook files based on the "Filename" column.
Thank you again for all your help!!
The result should not re-sort any of the data in the original or in the separated files, and the order or number of columns, or names of columns should not matter to the macro except with respect to the "Filename" column. The file names will take on whatever name for that row is noted in the file name column, regardless of whether that column is column A or another one.
The code should be able to make either worksheet tabs based on the "Filename" column, or workbook files based on the "Filename" column.
Thank you again for all your help!!
I really would like thw whole procedure. For instance I do not understand why you need to combine the data from several workbooks into one sheet, then split it into separate sheets.
ASKER
This should be a generic separator in should work on any sheet regardless of the project
As I said, I wanted to review each step of the process but I'm getting nowhere like this.
As far as I can see the code I have written works, the problem you have is not caused by the code but the volume of data being processed.
As far as I can see the code I have written works, the problem you have is not caused by the code but the volume of data being processed.
ASKER
That is correct. It is not working with that larger data set and that is the problem that I'm trying to solve. I have given you all the information about what the macro needs to do that I know. So far, it fails on this larger data set.
I still do not understand why you merge the data then separate it
ASKER
Oh. I think I understand your confusion now. However, I am confused as to the relevance of your question to the objective but I try to address it without getting off track.
The reason you might need to combine something, speaking generically is if the files are all part of the same dataset and you need to add an additional column or columns.
The reason you might need to re-separate is tomake the larger data more manageable from the standpoint of actionable items.
I hope that clarifies your question with respect to why I need a generic tool that can separate any spreadsheet into files or tabs, based on the contents of a column.
The reason you might need to combine something, speaking generically is if the files are all part of the same dataset and you need to add an additional column or columns.
The reason you might need to re-separate is tomake the larger data more manageable from the standpoint of actionable items.
I hope that clarifies your question with respect to why I need a generic tool that can separate any spreadsheet into files or tabs, based on the contents of a column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, thank you
I have run some code of mine that I have used for years to split data.
Both sets of code result in EXcel not responding.
As I said about our previous project I think the amount of dta to be processed is more that Excel can cope with.
I'll try a few more experiments, there is a possibility that the workbook is corrupted in some way.