Paste columnwidths in Xl2k problem

[Stuart]

'Set 2 variables in the activesheet:

Set header_rng = ActiveSheet.Range("A1:G1")
header_rng.Copy
Set col_widths = ActiveSheet.Columns("A:J")
col_widths.Copy

'Add a new workbook, which will be active
Workbooks.Add
'followed by code which creates new sheets,
'names the sheets, and deletes the sheets (1 and
'2) created when the book was added.
'The new workbook is still the active workbook
'so:

For Each ws In ActiveWorkbook.Worksheets
With ws.Columns("A:I")
col_widths.PasteSpecial Paste:=8
End With

With ws.Range("A1")
header_rng.PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With
Next

col_widths.PasteSpecial Paste:=8 gives error:
PasteSpecial method of Range class failed

and
header_rng.PasteSpecial Paste:=xlAll
tells me that the copy/paste areas are not the same
size.

Very basic questions, I'm afraid but any help and
explanation would be much appreciated, please.

Regards.

 

[Ed]

Try selecting just Range("A1") and let Excel fill in as the Paste happens,
rather than trying to define the range of the paste.

HTH
Ed

 

[Stuart]

Thanks, but this time it was
PasteSpecial method of Range class failed
for both paste actions.

Regards.

 

[Tom Ogilvy]

col_widths.copy
For Each ws In ActiveWorkbook.Worksheets
With ws.Columns("A:I")
.PasteSpecial Paste:=8
End With
Next

header_rng.copy
For Each ws In Activeworkbook.Worksheets
With ws.Range("A1")
.PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With
Next