Rearrange columns by header, add formulas and formats with micro

[Emloucon]

PLEASE HELP!

I am trying to write a macro which will be used on multiple spread sheets in excel.

I need to rearrange the columns and it may be necessary to add column to.
certain columns will need extra digit added and a formula needs to be done too.

I think the best way to do this is an example:

How I receive the spread sheet -

Column A B C D E F
[TABLE="width: 1241"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CODE[/TD]
[TD]DESTINATION[/TD]
[TD]AT USD ($)[/TD]
[TD] DATE[/TD]
[TD]STATUS[/TD]
[TD]ROUTE[/TD]
[/TR]
[TR]
[TD]7840[/TD]
[TD]ABKHAZIA[/TD]
[TD="align: right"]0.0483 [/TD]
[TD] 06/08/2013[/TD]
[TD]DECREASE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]79407[/TD]
[TD]ABKHAZIA-MOBILE AMOBILE[/TD]
[TD="align: right"]0.0488 [/TD]
[TD] 06/08/2013[/TD]
[TD]DECREASE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]79409[/TD]
[TD]ABKHAZIA-MOBILE AQUAFON[/TD]
[TD="align: right"]0.0488 [/TD]
[TD] 06/08/2013[/TD]
[TD]DECREASE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]672[/TD]
[TD]AET ANTARCTICA[/TD]
[TD="align: right"]0.9805 [/TD]
[TD] 06/08/2013[/TD]
[TD]NO CHANGE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67212[/TD]
[TD]AET ANTARCTICA CASEY BASE[/TD]
[TD="align: right"]0.9080 [/TD]
[TD] 06/08/2013[/TD]
[TD]DECREASE[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This then need a micro to be applied so it has this set out -

Column A B C D E F G H
Rates Prefix Area Prefix Rate Type Area Name Billing Rate Billing Cycal Min Cost Lock type

(empty) (column A above (alway states (column B above) (column C above (always 1/1 (column C above ("No Lock" on all)
with two "0" international) divided by 6 with unless column with "000" added
added to front) "000" added so D has mexico to creat 7 didgits)
each 7 digits long) in field which
makes it 60/60)


It would be better if the reorganizer micro was separate from the formula and formate macro.

I would love it if someone could help me because the macro iv written just isn't working and I don't understand why

Thank you

 

[hippiehacker]

just post your code and we might be able to fix it.

 

[Emloucon]

Well at the moment, iv just been "copying and pasting" but it would be easier if i could arrange it by the heading on the spread sheet.

for formatting the cells the only one im have real bad trouble is making it 7 digits for some reason if it has a 0 at the end it only makes it 6 digits but the number cant change just 0 to be added

This is what i have at the moment



Sub Macro1()
'
' Macro1 Macro
'


'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 19.43
Range("A1").Select
ActiveCell.FormulaR1C1 = "RATE PREFIX"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "RATE TYPE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "BILLING RATE"
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Range("F1").Select
ActiveCell.FormulaR1C1 = "BILLING CYCAL"
Range("G1").Select
ActiveCell.FormulaR1C1 = "MIN COST"
Range("H1").Select
ActiveCell.FormulaR1C1 = "LOCK TYPE"
Columns("G:G").Select
Selection.ColumnWidth = 13.57
Columns("H:H").ColumnWidth = 12.43
Range("A1:H1").Select
With Selection.Font
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B7").Select
End Sub


For the arrange of columns and for the "area prefix" where two 0 needed to be added at the beginning


Sub CODE_IMPORT()
'
' CODE_IMPORT Macro
'


'
Columns("B:B").Select
Selection.Copy
Columns("K:K").Select
ActiveSheet.Paste
Range("L2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=""00""&RC[-1]"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L25740"), Type:=xlFillDefault
Range("L2:L25740").Select
ActiveWindow.SmallScroll Down:=-60
ActiveWindow.ScrollRow = 25656
ActiveWindow.ScrollRow = 25688
ActiveWindow.ScrollRow = 25721
Range("B25740").Select
ActiveCell.FormulaR1C1 = "=RC[10]"
Range("B25740").Select
Selection.AutoFill Destination:=Range("B1:B25740"), Type:=xlFillDefault
Range("B1:B25740").Select
ActiveWindow.SmallScroll Down:=-21
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[10]"
Range("B1").Select
ActiveCell.FormulaR1C1 = "CODE"
Range("C6").Select
End Sub



Because i have to do several for each spread sheet im getting really confused!