Excel 2007 Paste Formats error "selection too large"

[blackshh]

I have a macro that copies one line (a named range) and pastes the format from this line to 1000 lines (also a neamed range). It worked no problem in Excel 2000 and 2003, but is giving a "run time error 1004 " "PasteSpecial Method of Range class failed". When I do this same process manually, I get the error "Selection too large". Does anyone know how to make this work in Excel 2007?
thanks

Here is the code...
.Range("rng_FormatCopy1").Copy
.Range("rng_FormatPaste1").PasteSpecial xlFormats

 

[JoeMo]

Can you show a few more lines of code above and below the ones you posted? How many columns wide are the named ranges?

 

[blackshh]

.Range("rng_Report").Copy
Application.DisplayAlerts = False
.Range("rng_Input_Report").PasteSpecial xlValues
.Range("rng_FormulaBudget").Copy
.Range("rng_FormulaPasteBudget").PasteSpecial xlFormulas
.Range("rng_FormulaVariance").Copy
.Range("rng_FormulaPasteVariance").PasteSpecial xlFormulas

.Range("rng_FormatCopy1").Copy
.Range("rng_FormatPaste1").PasteSpecial xlFormats
.Range("rng_FormatDesc").Copy
.Range("rng_FormatPasteDesc").PasteSpecial xlFormats
.Range("rng_FormatCopy2").Copy
.Range("rng_FormatPaste2").PasteSpecial xlFormats


Application.CutCopyMode = False
End With
**************
There is no problem with the copy and pastespecial xlValues or xlFormulas, only the xlFormats.

the named range for the copy is
D$14:H$14
The paste range is
D$28:H$1026

 

[blackshh]

.Range("rng_FormatDesc").Copy
.Range("rng_FormatPasteDesc").PasteSpecial xlFormats

This part works fine.
The copy range is $I$14
the paste range is $I$28:$J$1026

 

[JoeMo]

I don't understand why you are having this problem. I ran the test code below in Excel 2007 w/o any issues:

Sub test15()
With ActiveSheet
    .Range("rng_A").Copy
    .Range("rng_B").PasteSpecial xlFormats
End With
End Sub

Where rng_A is $D$14:$H$14 and rng_B is $D$28:$H$1026.
Did you check to see if your named ranges have any errors in the Refers To: column of Name Manager? Are the names sheet-level or workbook-level names?

 

[blackshh]

I don't know how to check to see if the named ranges are sheet level or workbook level.

 

[JoeMo]

Go to the Formulas menu and select Name Manager in the Defined Names section. Find the names and see what's listed in the Scope column for each name.

 

[blackshh]

They are workbook. There is only one sheet though.

 

[JoeMo]

Did you check to see if the specific named ranges in question have errors?
To do this: Go to the Formulas menu and select Name Manager in the Defined Names section. Find the names and see what's listed in the "Refers To" column (just to the left of the Scope column) for each name.