VBA UserForm and array Help needed

Please attach files to the post here, not external sites

Please accept my apologies for that.

I have attached the files already

Thanks for looking into it

I've started tidying up the code in the UserForm, I need to know a bit more about what the Split is for
12-10-15-40-FT.xlsm

Hello Mr. Roy,

Thank you for the quick support

The whole idea is like when the user form loaded the cells in the header row will be listed in Listbox1 as entries and from there you can select the column names which needed to be exported by Double clicking it. Double clicks will copy the selected entries to Listbox2 where you can adjust the positioning using the UP and DOWN Command Buttons.

ComboBox1 will list the same Column names for selecting the reference column based on that the entire data will be split in to different files

Combobox2 will help us to select the starting row of the data and using Combobox3 we can select the file type to be Exported
and licking the split button will shoot the Getfolder() function to select the Folder and the selected columns in the data will split into files based on Reference column.

Eg: as one of many scenarios we need to enter the attached scan report in the system by HHT Used. We will be exporting only Barcode and Qty in this Scenario. So we need to Split data based on HHT Name to CSV files but the exported data will be having only Barcode and Qty Fields

I am working as an Inventory Executive and Distribution Coordinator where we will be dealing with customers and suppliers of Different Brands. This function will make our work so much easy as we are spending half of our working day to split and clean the data to be imorted into the ERP.

Thank you Very Much

Please check the attached file.

I have amended the code that loads your ListBox and ComboBoxes, let me know if this is correct.

I've also fixed the GetFolder for the split button.

I need to have a look at what the rest of the code is attempting when you confirm the changes are OK

Hello Mr. Roy,

Please can you attach the file again as I cannot see any attachments

Thanks

Sorry, I attached it earlier but I have made a slight change since.
12-10-15-40-FT.xlsm

Hi,

Looks good so far.

Please go ahead,

Thank you So Much

Are you creating one csv containing the select columns or one csv for each of the selected columns?

it will be one csv per reference containing selected columns.

eg:

in the above eg. One csv will contain all the barcodes scanned with scanned qty  using HHT 109 and will save the CSV names 109.csv in the selected folder

Please see the attache file for reference
109.xlsx

I can't see how you select to filter the data by 109

I've finished this and it works fine as far as I can see. I need to know where HHT 109 is set in the form, currently it is coded as a fixed value. I need to change that.

Check this for now.
12-10-15-40-FT.xlsm

Hi Mr. Roy,

So far so good. its working perfectly as i needed for one Selected Criteria.

What I Needed is to each unique value in the selected reference col to be filtered and copied to another workbook and saved with the reference name, but the exported values should be the entries in Listbox2 and the order should be same order as Listbox2

The following code might give you an idea what i am trying to do basically.

This code will split the entire data in to workbooks based on the given Column Number. I am trying to do an advanced version of the same as my data will be more dynamic to give as i am getting data from ERP, Different Suppliers, Different Customers.

Please have a look and try this.

This may help us figure out a solution

Sub ParseItems()

Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String, SvPath As String

   Set ws = ActiveSheet

    SvPath = "C:\Users\User\Desktop\"

    vTitles = "A1:Z1"
    
   

   vCol = Application.InputBox("What column to split data by? " & vbLf _
        & vbLf & "(A=1, B=2, C=3, etc)", "Which column?", 1, Type:=1)
   If vCol = 0 Then Exit Sub

   LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row

   Application.ScreenUpdating = False
 

    ws.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True

    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" & Rows.Count).SpecialCells(xlCellTypeConstants))

    ws.Range("EE:EE").Clear

    ws.Range(vTitles).AutoFilter

    For Itm = 1 To UBound(MyArr)
        ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
        ws.Range("A1:A" & LR).EntireRow.Copy
        Workbooks.Add
        Range("A1").PasteSpecial xlPasteAll
        Cells.Columns.AutoFit
        MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 1
        
        ActiveWorkbook.SaveAs SvPath & MyArr(Itm), xlNormal
       
        ActiveWorkbook.Close False
        
        ws.Range(vTitles).AutoFilter Field:=vCol
    Next Itm

    ws.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Please check they match"
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Thank You

Sorry, I'm not interested in seeing other people's code.

Are you saying that you need one new file for each unique entry in the selected column?

Awesome, Awesome!!!

You are a genius man!!!

This is the function i was looking for. this is what i needed

Do you know how many hours you have just saved from my day to day job. Hours!!!!!

Thank You So Much

Sorry,

Can you please check this Combobox3 (Data Starts from Row) is not working in the form and also Changing the positions in List box does not make any differences in the exported data. Can you please check if we can follow the adjusted positioning of the columns in Exported Files

Thank you sir,

Why would you want to change the positions of the columns? If you start copying columns one at a time then the code will slow down.

I'm not sure what you want the row position to be changed for.

Hi,

It was just an idea like if we can adjust the positioning then from one screen itself the job will be done. but even without that it can be used with a little bit manual positioning. may be we can ignore that as I cannot use a script which lags my entire system. Thanks for the valuable advise.

Please can you help me figure out the data start row Combobox so I dont want to put you in more struggle

Thank you for your valuable support in this

How do you want to use that row,? Do you want to delete all rows above it, except the header row?

there might be more than one row above header row. so the filtered data should be whatever below the header row. may be we need to add one more combobox to define the header row.. Any way this might be enough. the rest i will do manually as that's not a big issue.

Thank you very much for your quick support.

Thank you, you are amazing

Is the position of the data random?

When yoy there may be more than one row above the header row, are these rows empty?

Yes sir, Some times the files from suppliers will be having random positioning, say Barcodes at the end like that. so and we will need to save that file as a reference too. so I was thinking like if I extract the data needed from the same file without copying and reopening it.

And also for the header part. Please refer to the attached file which i am exporting Sales report every sunday for suppliers. Normaly I will be doing like either by exporting whole sales report by week from ERP and splitting it by Supplier ( will take more than an hour to do that as I have more than 100 Suppliers consisting more than 150 Brands) or Export the sales report by Supplier ( Still time consuming).

Some reports having more reference in top before Header row. like 2 or three rows with dates, filter criteria(Brand, Season) like that.

I am looking for a one stop solution for both of these scenarios.

Do you think if adding one more combobox to select the header row and using the same Data starts row to will help us to get the exported files having whatever rows above header row included. (Just an Idea)

Please advise

Thank you
AL-BOOM-MARINE-SALES-REPORT-20-05-1.xlsx

I'll have a look later and decide the best way to sort this

Thank you. Thank you very much for this help

The problem is that the headers on the current examples are different to the headers on the example that I have been working on. In fact if you want to copy the title at the top it requires quite a few changes to the current code. It's really important when asking for help that your example file is an exact match of what youare working with.

I'll have a look later.

Are the sheets in the new workbook typical of the sheets you will be working with?

Some of them. But if there is an option to select the header row and if all the rows above the header row will be copied in the exported files, then that will be fine.

I was thinking like adding one more combobox to define Header row and if you can amend a little bit of your code to copy the rows above header row then that will be a great help

We can use ComboBox 3 as header row, but it's not that simple.

The merged cells are making this more difficult, especially if they are not always there.

It's difficult to write the code to copy the data and the cells above it, especially not knowing what the upper cells contain. I s there no control over the source sheets?

Hi Mr.Roy,

We don't have any control over the source sheets as the files will be send from different suppliers and customers as I Mentioned earlier. (Imagine more than a hundred suppliers and customers exporting files from there ERP systems and forwarding to us as Implimentation files and Purchase Orders)

But so far what ever you have helped was a great great job. I can do the rest as a manual work as the main functions has been resolved and refined. that was a great help. it can save hours a week for me.

Thank you very much

I really appreciate it

My prime issue was I am always receiving files from different people in different formats. I was looking for a one stop solution to make and split the files into a ERP readable solution or to split files with selected format. Mr Roy helped me to get more than I have Imagined

Pleased to help. I'll post a version that uses ComboBox3 to determine the header row