'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.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003

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" wrote in message
...
'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.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003

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

Regards.

"Ed" wrote in message
...
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" wrote in message
...
'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.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003

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

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
Thanks, but this time it was
PasteSpecial method of Range class failed
for both paste actions.

Regards.

"Ed" wrote in message
...
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" wrote in message
...
'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.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003