Need help with macro

[mchong]

Guys,

Need help with a particulary thorny data table. And hoping someone here can give me a hand.

I've got data generated from our system that comes it a text file. This needs to be formatted into a report, so instead of doing this manually each time, we would like a macro to automate the process. Only problem here is that that the number of columns in the report always varies so I cannot get a recorded macro to work on this since the different number of columns just throws the macro off track.

How do I modify / write the macro so that regardless of changes to the number of columns, it will be able to select the correct ranges, add / delete columns in the right places?

If it helps, here's a sample of the code:-

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Rows("8:8").Select
Selection.AutoFilter
Range("A8").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:$BX$10000"), , xlYes).Name _
= "B13Table"
Columns("P:P").Select
Range("B13Table[[#Headers],[Movement Loading]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "DELIVERY"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""",RC[-2],RC[-1])"
Columns("S:S").Select
Range("B13Table[[#Headers],[Units(Qty.)]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "MODE"
Range("S9").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(B13Table[[#This Row],[Movement Loading]],""/"",B13Table[[#This Row],[Movement Discharge]])"
Range("S10").Select
Columns("X:X").Select
Range("B13Table[[#Headers],[CBM]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "OP"
Range("X9").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""CARRIER"",""COC"",""SOC"")"
Columns("AA:AA").Select
Range("B13Table[[#Headers],[Commodity]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "WT (TONNES)"
Range("AA9").Select
ActiveCell.FormulaR1C1 = "=B13Table[[#This Row],[Weight(Gross)]]/1000"
Range("AA10").Select
Columns("BX:BX").Select
Range("B13Table[[#Headers],[Charge Currency]]").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B13Table[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "CODE"
Range("BX9").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""OCF"",""FREIGHT"",IF(RC[-3]=""SFB"",""FREIGHT"",IF(RC[-3]=""SFC"",""FREIGHT"",IF(RC[-3]=""SWE"",""FREIGHT"",IF(RC[-3]=""BAF"",""BAF"","""")))))"
Range("BX10").Select
ActiveWindow.SmallScroll Down:=3
Range("B13Table[WT (TONNES)]").Select
Selection.NumberFormat = "#,##0.00"
Range("B13Table[[#Headers],[Container Type]]").Select
ActiveCell.FormulaR1C1 = "TYPE"
Range("B13Table[[#Headers],[Consigee]]").Select
ActiveCell.FormulaR1C1 = "CONSIGNEE"
Range("B13Table[[#Headers],[Units(Qty.)]]").Select
ActiveCell.FormulaR1C1 = "QTY"
Range("B13Table[[#Headers],[Doc Amount]]").Select
ActiveCell.FormulaR1C1 = "FRT"
End Sub

Thanks in advance!

Marco.

 

[stanleydgromjr]

mchong,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


And, please post all your macro code using code tags.

At the beginning of your posted code, enter the following without the quote marks:
["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]


Have a great day,
Stan

 

[mchong]

Stan,

THanks for the reply but unforunately can't seem to get exceljeanie to work. I get a message that says "more than 500cells inrange for forum" process abandoned. What do i need to do?

Marco.

 

[stanleydgromjr]

mchong,

Try pasting smaller portions of your sheets.

Or:

See my Private Message to you (top right hand corner of MrExcel, Welcome, mchong.


Have a great day,
Stan

 

[mchong]

Thanks Stanley and all, have managed to solve this by looking around and fiddling on my own. Many thanks!