PasteSpecial problem

[Jim Tibbetts]

Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end). It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW TOTAL.
Range("WeeklyInfo!D1059").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.UsedRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,

 

[Chip Pearson]

Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Ron de Bruin]

And if you use Excel 2000 you must use the number 8 (Bug in 2000)
But 8 will work in every version

 

[Jim Tibbetts]

Hi Chip. Thanks for the quick reply. The code that was in my macro was from
one that I had recorded. I did make the change you suggested, but the macro
still hangs on that line. Any other ideas?

 

[Jim Tibbetts]

Ron - I am using XL2000, but I don't understand what you mean by using the
number 8. Can you explain further?

 

[Ron de Bruin]

Hi Jim

Use the value
PasteSpecial Paste:=8

Instead of the name
PasteSpecial Paste:=xlPasteColumnWidths

This is a bug in Excel 2000 and the workaround is to use the value

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Ron - I am using XL2000, but I don't understand what you mean by using the
number 8. Can you explain further?

 

[Ron de Bruin]

If I remember correct xlPasteValidation have the same problem in Excel 2000
Use 6 then

 

[Chip Pearson]

The code worked fine for me. What do you actually mean by "hangs on that
line"? You might try using real range reference rather than Selection. It
(almost) never necessary to Select anything in VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Jim Tibbetts]

Got it. Thanks for the help Ron.
--
Jim T

If I remember correct xlPasteValidation have the same problem in Excel 2000
Use 6 then