Macro for text segregation

Cris_93

New Member
Joined
Nov 1, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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:
1587124400032.png


Well this info needs to be readjusted into the following format (7 columns).
RT CodeDescriptionCustomerMarketFactTypeUnit24/05/2020
RTE015 EDEN BEEF 6oZ MEGA BURGER 680G 336594011OCADODEFAULT[Live] Ocado Rolling forecastP
322​
RTE006 EDEN BEEF ROAST RUMP JOINTOCADODEFAULT[Live] Ocado Rolling forecastP
170.16​
RTE001 EDEN LAMB RACK 278703011OCADODEFAULT[Live] Ocado Rolling forecastP
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]
 
Peter it is working perfectly now!

Thanks a lot!! :)

Kind Regards,
Cristian
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top