Run-time error 1004: Too many different cell formats

I'm running Excel 2003. I have 2 workbooks that I'm trying to combine into
1. I've taken the macro from one and inserted it into the other workbook.
The macro opens up reports and copies all of the information (including
formatting) using:

Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
ActiveWindow.SmallScroll Down:=-45
etc.....

The macro makes it through 21 of 24 reports before giving the run-time
error. I've tried making sure the formatting in the existing sheets (not the
ones being copied) is all the same but nothing seems to help. The macro runs
fine in the workbook without other sheets in it. The sheets being copied are
all formatted the same.

Is there some code I can add to my macro to strip out any unused formatting?

I'm a vba/macro novice and will appreciate all help!

Thanks!

Randy

Update: I've found a macro that will remove any unused number formatting but
what I really needs is a macro that will clean-up cell formatting.

Thanks again!

Randy

"Randy" wrote:

> I'm running Excel 2003. I have 2 workbooks that I'm trying to combine into
> 1. I've taken the macro from one and inserted it into the other workbook.
> The macro opens up reports and copies all of the information (including
> formatting) using:
>
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
> ActiveWindow.SmallScroll Down:=-45
> etc.....
>
> The macro makes it through 21 of 24 reports before giving the run-time
> error. I've tried making sure the formatting in the existing sheets (not the
> ones being copied) is all the same but nothing seems to help. The macro runs
> fine in the workbook without other sheets in it. The sheets being copied are
> all formatted the same.
>
> Is there some code I can add to my macro to strip out any unused formatting?
>
> I'm a vba/macro novice and will appreciate all help!
>
> Thanks!
>
> Randy