Excel formula wont work

Hi In a macro i am using the same formula in 2 columns at one it works and in the other it does not work. Not sure whats going on any help?

I have attached the sample file herewith.

Here is the macro code

Any help is appreciated. Cheers

___________________________________________________________________________
Sub RunCodeOnAllXLSFiles()

Dim Sht As Worksheet
Dim Name1 As String

Name1 = ThisWorkbook.Name



For Each Sht In ActiveWorkbook.Worksheets

Sht.Activate
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEN(RC[-1])>0,""%%%%%"","""")"
Range("B1").Select
Selection.Copy
Range("B1:B500").Select
ActiveSheet.Paste

Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(LEN(RC[-1])>0,""$$$$$"","""")"
Range("D1").Select
Selection.Copy
Range("D1:D500").Select
ActiveSheet.Paste

Next Sht



For Each Sht In ActiveWorkbook.Worksheets

Sht.Activate
'
ChDir "C:\Men"
ActiveWorkbook.SaveAs Filename:= _
"C:\Men\" & Name1 & "_" & Sht.Name & ".txt" _
, FileFormat:=xlTextMSDOS, CreateBackup:=False
Next Sht
End Sub
______________________________________________________________________

Hi

Maybe

Please Login or Register  to view this content.