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:-
Thanks in advance!
Marco.
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:-
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.
Last edited by a moderator: