dear all, pls help.

Branch No Customer Def Value Company Date
14213 0091 000100 ABC 15102004

should be this
142130091000100ABC 15102004

but when run macro is this
14213009100010015102004


thanks alot.


sample as below...

Sub MHeader1()
'
' MHeader1 Macro
' Macro recorded 15/10/2004 by
'

'
ActiveCell.FormulaR1C1 = _
"=IF(LEN(R[-2]C)<20,R[-2]C&REPT("" "",20-LEN(R[-2]C)),LEFT(R[-2]C,20))"
Range("D4").Select
Selection.Copy
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-11],RC[-10],RC[-9],R[4]C[-8],R[4]C[-7])"
Range("L2").Select
Selection.Copy
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Sheets("Final").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Header").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("M2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("L2").Select
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=-7
Range("E6").Select
Selection.ClearContents
Range("D6").Select
Selection.ClearContents
Range("D4").Select
Selection.ClearContents
Range("D2").Select
Selection.ClearContents
End Sub

Try this..
it's a bit shorter
hope that isn't a probelm
<g
Sub Macro1()

Range("Final!A1").Value = _
Format(Range("Header!a2"), "00000") & _
Format(Range("Header!b2"), "0000") & _
Format(Range("Header!c2"), "000000") & _
Left(Range("Header!d2") & Space(20), 20) & _
Format(Range("Header!e2"), "00000000")
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(tango) wrote:

dear all, pls help.

Branch No Customer Def Value Company Date
14213 0091 000100 ABC 15102004

should be this
142130091000100ABC 15102004

but when run macro is this
14213009100010015102004


thanks alot.