Problem with simple macro that works in 2003 not 2000?

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,035
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 ?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209

ADVERTISEMENT

It is in XL2000

change xlColumnWidths to 8 eg

Paste:=8

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I've just tried xlColumnWidths in the Object Browser, using XL2000 and 'No Items Found' was the result.:eek:
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209

ADVERTISEMENT

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

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Ivan

That's quite right, just tried it.:)

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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
 

Forum statistics

Threads
1,136,510
Messages
5,676,281
Members
419,617
Latest member
Shane50GT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top