Excel issue VBA - copy and past content

[hbvba]

Hello,

I have a problem with an Excel extraction made via my ERP. The file contains 30 columns of data, and the item designation column is the 10th column.
Sometimes, due to an anomaly in the ERP that we can't fix, all the other 20 columns are put in the designation cell separated by “;” (when the problem occurs, the cell in question recovers an indefinite number of rows from the extraction, sometimes 20 rows and sometimes 300 rows).
So I'd like to set up a VBA that identifies the problematic designations and separates the rows automatically.
I've managed to set up the filter to identify the designations in question, but I'm stuck at one point: I can't copy the contents (this is important, copying the cell isn't enough), go to another page and paste, keeping only the text.

I've tried range.copy and I've also tried

Range(“K4”).Select
Application.SendKeys “^A
Application.SendKeys “^C”
Sheets(“Test”).Select
Range(“K1”).Select
ActiveSheet.PasteSpecial Format:=“Unicode text”, Link:=False

but it doesn't work.

I've attached an example. The first sheet (issue) corresponds to a part of the extraction, page two (solution) is the result I'd like to have for this step and page three (final) is what I'd like to have once the VBA is finished.
When this problem is solved, I have another problem. When I paste the data on the second page, the first line corresponds to :
- the name and the data sequence of the cell that causes the problem on the first page. (blue color see "solution" page and "final" page)
- The data sequence after the designation of the problem cell on the first page corresponds to the data in the last line of the second page. (orange color see "issue" and "final" page)

I've added a color code to help you identify the final result on the third page (final).

 

[Micron]

I can barely see the data in the first pic and the others are not that much better.

So I'd like to set up a VBA that identifies the problematic designations

For the first issue, it might be easier and more reliable to do something like double click on the cell that contains too much data. Based on what I can see, perhaps the process would be something like
- put the entire cell contents that you double clicked on into a variable (if it's the correct column)
Start of loop
- if the 1st value is guaranteed to be a date (which appear to all be of the same format, i.e. 10 characters) then
- start at 11th character position and get the remaining string. Pass that to a function that uses RegEx that finds the next date
- use Instr to find the position of that date (store this number [##] as it would be needed abt 5 steps later)
- use Mid function to get from the variable what should have been a row of data (up to the next date)
- pass that to an array, separated by ";"
- loop over the array and put each element into a column on the clicked row (using Offset in a loop)
- insert a row below
- use Mid function again with ##, rebuild the string variable with everything from the next date on, thus stripping out what was entered into the columns
- repeat the loop over the inserted row
Note - that is a rough outline which likely doesn't encompass everything, such as maybe not inserting a row after the last row in the loop. It wouldn't surprise me if someone came by with a better idea.