Macro for text segregation

[Cris_93]

Hello excel masters :D

I need your help in solving what I believe is a easy fix. I'm working in developing a macro that will copy the information from an exported file and organize it by columns.

The exported info comes as below:

Well this info needs to be readjusted into the following format (7 columns).

RT Code	Description	Customer	Market	FactType	Unit	24/05/2020
RTE015	EDEN BEEF 6oZ MEGA BURGER 680G 336594011	OCADO	DEFAULT	[Live] Ocado Rolling forecast	P	322​
RTE006	EDEN BEEF ROAST RUMP JOINT	OCADO	DEFAULT	[Live] Ocado Rolling forecast	P	170.16​
RTE001	EDEN LAMB RACK 278703011	OCADO	DEFAULT	[Live] Ocado Rolling forecast	P	772.84​

Can you please help me with this?

This is the code I have being working on but it is not working:

Code[
Sub RollingForecastOcado()

Dim repFl As String
Dim rpSh As String
Dim btRw As Long



Application.ScreenUpdating = False
repFl = ActiveWorkbook.Name
rpSh = ActiveSheet.Name
Sheets("Rolling Forecast Ocado").Select
Cells.Select
Selection.ClearContents


Workbooks.Open Filename:= _
"\\IEDRGSFS01\data\Procurement\Quintiq DP Exports\RollingforecastExport.csv"
btRw = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("A1:P" & btRw).Select
Range("A1:P" & btRw).Select
Selection.Copy
Windows(repFl).Activate
Sheets("Rolling Forecast Ocado").Select
Range("A1:P" & btRw).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.AutoFit
Windows("RollingforecastExport.csv").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = False


Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "RT Code"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],6)"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A1638")
Range("A2:A1638").Select
Range("A1638").Select
Selection.AutoFill Destination:=Range("A1638:A1644"), Type:=xlFillDefault
Range("A1638:A1644").Select
Range("A1638").Select
Selection.AutoFill Destination:=Range("A1638:A3383"), Type:=xlFillDefault
Range("A1638:A3383").Select
ActiveWindow.ScrollRow = 3199
ActiveWindow.ScrollRow = 3058
ActiveWindow.ScrollRow = 1
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$3383").AutoFilter Field:=5, Criteria1:=Array( _
"TESCO", "TESCO NI", "="), Operator:=xlFilterValues
Rows("2:3379").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$H$112").AutoFilter Field:=5
Range("E6").Select
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("F:G").Select
Selection.Delete Shift:=xlToLeft
Columns("F:G").EntireColumn.AutoFit
Columns("E:E").ColumnWidth = 10.64
Columns("D:D").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
End Sub]

 

[Cris_93]

Peter it is working perfectly now!

Thanks a lot!!

Kind Regards,
Cristian

 

[Peter_SSs]

Great! Thanks for the confirmation.