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.

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

Open in new window

160408-To-Be-Separated-EE-testing.xlsb

Roy Cox

I've run your code and then amended it to attempt to make it more efficient.

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.

Ted Penner

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.

Roy Cox

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.

Ted Penner

ASKER

Understood. What I am hoping is that someone has found some approaches to dealing with this well known problem of Excel.

Roy Cox

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.

Ted Penner

ASKER

No, for this purpose it needs to be able to do files or tabs. The files process is not even working right

Roy Cox

So tell me the steps that you need to do to achieve the end result.

Ted Penner

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.

Roy Cox

I want to know each step of the process so that I can look at it without trying to amend other people's code.

Ted Penner

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!!

Roy Cox

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.

Ted Penner

ASKER

This should be a generic separator in should work on any sheet regardless of the project

Roy Cox

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.

Ted Penner

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.

Roy Cox

I still do not understand why you merge the data then separate it

Ted Penner

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.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ted Penner

ASKER

ok, thank you