Problem with simple macro that works in 2003 not 2000?

[Pauljj]

I am using this code, which works perfectly for 2003

Sub CSVLBAFEB()
'
' CSVLBASEP Macro
' Macro recorded 14/09/2006 by Paul Hailes
'

'
Columns("FD:FF").Select
Selection.Copy
Workbooks.Add
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "L:\Payroll"
ActiveWorkbook.SaveAs Filename:="L:\Payroll\LBAFEB07.csv", FileFormat:=xlCSV _
, CreateBackup:=False
End Sub




It seems to fail on the pasting of column Widths, even when i try to record a macro, by copying and pasting widths it fails? I dont understand...does anyone else ?

 

[Datsmart]

I may be wrong, but I don't believe xlPasteColumnWidths was available in Excel 2000. That would be why your code fails when run on that version.

 

[Pauljj]

The option is there under paster special for 2000 ?

 

[erik.van.geit]

I may be wrong, but I don't believe xlPasteColumnWidths was available in Excel 2000. That would be why your code fails when run on that version.

you're right, it wasn't in 2000

workaround would be to set the widths one by one
example

Sub test()
Dim i As Integer

    For i = 3 To 11
    Sheets(1).Columns(i).ColumnWidth = Sheets(2).Columns(i).ColumnWidth
    Next i

End Sub

kind regards,
Erik

 

[Ivan F Moala]

It is in XL2000

change xlColumnWidths to 8 eg

Paste:=8

Selection.PasteSpecial Paste:=8, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False

 

[Norie]

I've just tried xlColumnWidths in the Object Browser, using XL2000 and 'No Items Found' was the result.

 

[Ivan F Moala]

I've just tried xlColumnWidths in the Object Browser, using XL2000 and 'No Items Found' was the result.

If you record a macro you will see it displays xlcolumnwidth BUT bugs out when running it. This is a known bug, hence my post to change the xlcolumnwidth to it's constant.

 

[Norie]

Ivan

That's quite right, just tried it.

Bit strange that the macro recorder creates code with a constant that doesn't apparently exist.

 

[erik.van.geit]

If you record a macro you will see it displays xlcolumnwidth BUT bugs out when running it. This is a known bug, hence my post to change the xlcolumnwidth to it's constant.

yeah !!
oh, it's about 3 years ago, I had the issue and solved it, completly forgot this one

Ivan, do you know if those are also "part of the bug" ?
xlPasteValidation
xlPasteFormulasAndNumberFormats
xlPasteValuesAndNumberFormats

best regards,
Erik

 

[Ivan F Moala]

If you record a macro you will see it displays xlcolumnwidth BUT bugs out when running it. This is a known bug, hence my post to change the xlcolumnwidth to it's constant.

yeah !!
oh, it's about 3 years ago, I had the issue and solved it, completly forgot this one

Ivan, do you know if those are also "part of the bug" ?
xlPasteValidation
xlPasteFormulasAndNumberFormats
xlPasteValuesAndNumberFormats

best regards,
Erik

xlpastevalidation = NO CONT it is infact
xlDataValidation=bug
the other 2 are not avail in xl2000