Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Copy all exept data
Hi
Below is a code snippet from my procedure. When NewNumAllNumAll, 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 -- Arvi Laanemets (When sending e-mail, use address arvil<Attarkon.ee) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Copy all exept data
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 "Arvi Laanemets" wrote in message ... Hi Below is a code snippet from my procedure. When NewNumAllNumAll, 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 -- Arvi Laanemets (When sending e-mail, use address arvil<Attarkon.ee) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Sort in Excel2000 | Excel Worksheet Functions | |||
Merge Excel2000 data to Word2000 | Excel Discussion (Misc queries) | |||
In EXCEL2000, How can I copy a file and make it usable in MS WORK | Excel Discussion (Misc queries) | |||
displaying filtered data on another sheet (Excel2000) | Excel Programming | |||
My workbook doesn't works on no other PC exept mine.... | Excel Programming |