Excel2000: Copy all exept data

[Arvi Laanemets]

Hi

Below is a code snippet from my procedure. When NewNumAll>NumAll, then it
copies last 4 rows of used range and pastes for 4*n rows down. The copied
range contains formatted cells (border, color, font, cell merging) + for
some cells the conditional formatting is used, or data validation, and in
some cells are formulas. All this must be copied. But sometimes there can be
also data (user entries) in some cells, which is the cause of my problem.
.....
Select Case NewNumAll
Case Is < NumAll
Sheets("JooksevKuu").Range((9 + 4 * NewNumAll) & ":" &
LastRow).Delete
Case Is > NumAll
Sheets("JooksevKuu").Range((LastRow - 3) & ":" & LastRow).Copy
(Sheets("JooksevKuu").Range((LastRow + 1) & ":" & 8 + 4 * NewNumAll))
End Select
.....

There is no problem until last 4 rows in table aren't filled by user. But
when they are, all those data are duplicated in added rows. Of-course it's
possible to clear all entries from added rows after copying (2 ranges to
clear), but maybe there is a way to copy formulas and formats by code,
leaving out all user entries.

Thanks in advance

 

[Ed]

Hi, Arvi. I use the following code to PasteSpecial column widths, formats,
and values. You can also put in code for other things - check VBA Help for
the Paste Special Method.

HTH
Ed

Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=8 ' this is column widths
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False