I need to copy down some header text, clear out a spreadsheet, and
then paste the headers back in. I can't get it to work. Anyone see a
problem with...

' copy down the headers we have in the user area
sheet.Range("A3:AZ3").Copy

' clear out anything that used to be in the sheet
sheet.Range("A3:IV5000").ClearContents
sheet.Range("A3:IV5000").ClearComments
sheet.Range("A3:IV5000").Borders.LineStyle = xlNone

' paste the headers back in
sheet.Paste sheet.Range("A3:AZ3")

If the range is empty, the user is presented with an error stating
that the Paste method failed. If it is not empty, they are instead
presented with the "Data on the Clipboard is not the same size and
shape...".

Anyone know how to do this reliably without user intervention?

Maury

Why not leave the header alone and clear out the data below it by changing
the following lines:

sheet.Range("A4:IV5000").ClearContents
sheet.Range("A4:IV5000").ClearComments
sheet.Range("A4:IV5000").Borders.LineStyle = xlNone

--
JT


"Maury Markowitz" wrote:

> I need to copy down some header text, clear out a spreadsheet, and
> then paste the headers back in. I can't get it to work. Anyone see a
> problem with...
>
> ' copy down the headers we have in the user area
> sheet.Range("A3:AZ3").Copy
>
> ' clear out anything that used to be in the sheet
> sheet.Range("A3:IV5000").ClearContents
> sheet.Range("A3:IV5000").ClearComments
> sheet.Range("A3:IV5000").Borders.LineStyle = xlNone
>
> ' paste the headers back in
> sheet.Paste sheet.Range("A3:AZ3")
>
> If the range is empty, the user is presented with an error stating
> that the Paste method failed. If it is not empty, they are instead
> presented with the "Data on the Clipboard is not the same size and
> shape...".
>
> Anyone know how to do this reliably without user intervention?
>
> Maury
>

Or you could do this:

sheet.Range("A3:AZ3").Copy
sheet.Cells.Clear
sheet.Range("A3").PasteSpecial Paste:=xlPasteValues

This assumes nothing in rows 1-2. If rows 1 or 2 contain data that
you want to retain, this suggestion fails.

"Maury Markowitz" wrote:

> I need to copy down some header text, clear out a spreadsheet, and
> then paste the headers back in. I can't get it to work. Anyone see a
> problem with...
>
> ' copy down the headers we have in the user area
> sheet.Range("A3:AZ3").Copy
>
> ' clear out anything that used to be in the sheet
> sheet.Range("A3:IV5000").ClearContents
> sheet.Range("A3:IV5000").ClearComments
> sheet.Range("A3:IV5000").Borders.LineStyle = xlNone
>
> ' paste the headers back in
> sheet.Paste sheet.Range("A3:AZ3")
>
> If the range is empty, the user is presented with an error stating
> that the Paste method failed. If it is not empty, they are instead
> presented with the "Data on the Clipboard is not the same size and
> shape...".
>
> Anyone know how to do this reliably without user intervention?
>
> Maury
>