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).
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).