VBA Macros to copy dynamic columns data to standard columns.

Hello hecgroups,

I am not sure I understood your requirement 100% but I "think" what I did is what you need. I have attached a file with a UserForm where the user will be prompt to select the sheet he/she wishes to copy from then do the matching column along with the main sheet. Just make sure to change the main sheet name in the top code of the UserForm. Let me know if this works for you or you need further assistence

Please Login or Register  to view this content.

Hi downloaded your file and and nankw83's file too

I have one question.
The lay-out for the new posting has nothing to do with the previous project I developed for you, correct?
Will the data of the files that you receive be appended to existing data or do you just want to import the new data but ordening it in the columns as you have now in Sheet5?

Am working on my idea

Update later

I have a project I developed for a client of mine that has exactly the same issues, data is offered in varying columns and I found a great solution for it.
I'm making some changes to simplify it so it works for your case, and yes, the manual actions can be 0 once the layouts are configured. It works straightforward and can if all is set correctly work unattended as far as it goed to column allocation.
What you did not answer is if the data you want to import is appended to an existing file with the sheet5 layout or that you want to create a new workbook for every import.

Will do my best, but give me a little time

This is a test file, it does not import anything it just helps you to allocate / map the data from the provider to your Sheet5 fields.
This form is in principle the same I have for the project I mentioned, it's a one-time action once you have mapped the columns you want.
I saves the mappings in a table and uses this when you actually want to import the data.
This part, the importing is not implemented.
What it does when you open the form it lists the table with in the worksheet Collumn_Allocation, the first three columns are :
A: The column header YOU have (Sheet 5) which for some reason has no spaces but that's no problem
B: will hold the mapped column number of the selected file to import
C: just holds the numberformat you have given the your corresponding fields, it reads if from the first data row in Sheet5

I tested both the Format sheets you have here and you see that there are two extra columns with a fieldname in it.
If you select the worksheet in the Allocation form it will read and check and show the columns numbers and the allocated fields
It's far from complete but it does work and recognize the headers as you can see from the list in Format1

Like I said it's far from complete but the manual work is only once and only if you receive other files.

Well, that is exactly what I have been explaining. I guess that did not come across as intended.
Like I said "Once the mappings are set you won't have to worry about them unless a new provider comes with an entirely different lay-out.
What I do need YOU to tell me is which cells in YOUR file will always need to be mapped, I can imagine that you want a minimum number of columns populated to be able to work with it.
Hope you understand my question.

Your sample file only has about 20 I think

Hai Keeb,

Yes. The beauty of your file is, it will automatically read the headers. If in future the column are increased the file read them automatically.

Please find the attached original headers.

Correction, you now have 30 fields.
This template test is just to show how you can map the fields
This is what I would then implement to use when actually filling in the data.

The idea is that the mapping is checked and if nothing missing the just do the import else warn and let you still add / correct the mappings and THEN read the source file (again)

Hope this makes sense to you

You did not set the cell format in the first row UNDER the header for the new headers, could you do so< this will acccelarate the correct formatting

I’m away fro my system but will look at it this evening
I’m sure it will be fine

Okay, I don't think you've formatted ROW2 correctly if I look at the header title but what about columns AG through BF ?

No problem, I’ve enough to figure out what it can be and yes, it can be changed afterwards the code is dynamic

Okay, this works for now, you will have to test and see if you can map the columns, this is a one-time action for each of the three formats you have included
Like I mentioned before I'm going to integrate this in a Template file and then all you will need to do is select the provider's file, open it and select the worksheet where the data is, press the button to populate the listbox on the right with the mapped columns and then run the copy action which is the code that nankw83 wrote.
You can see what it does.

Will have to see what you’ve done, works here

Found the reason why it failed, I used part of nankw83's code but forgot to take into account that I switched the two listboxes.
Should work as intended now

Hi have you had a chance to look at it?

Well, then I can give you the next one

When the allocation form is opened you first need to click the option button (red arrow points to the OptionButton) to open a file browser to select a folder and the a file from the list that will be generated.
The rest works the same as the previous one only this one uses a file YOU select.
The first thing is assign all the columns but that's a one time action.

The only thing missing here is a macro that saves the template sheet as a separate excel file for your use

Let me read what you've said and I'll try and answer sometime later.
For what concerns point 3, the header row is just to show that it doesn't matter where the header starts, it works.
You want row 1, just delete the two entire rows above the template sheet and you're back to row 1

I wrote placed my answers and comments in the attached word file

Hi, maybe I was not clear enough, you DO NOT have to clear empty rows above the header rows, all data ABOVE the header rows will be ignored, so if your provider sends a file with some data above the header row the macro code will ignore that.
The header row will be the first row that is not blank, but I could add a check to see if there are more than 4 columns filled then it will considered a header

I this this will help with the sorting

Well, I don’t know if it’s finalized, but it works
There will always be options to be desired and more fine tuning but this is works
Hope you enjoy it, it was above challenge and maybe I’ll put some work into it to make it useable as add-in
Who knows, others might profit from your questions and the result

No problem. Hope it also helps to stimulate your own programming skills
Happy coding,