Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
just copy the data, not the fuctions
Hi,
In the following code the data in every row from columns i:L from the current sheet is copied to the OnlyData sheet if there are any entries in column F of the current sheet. ------------------------------------------------------------------------ Dim range2_for_the_OnlyData As Range Set range2_for_the_OnlyData = Columns("f").SpecialCells(xlCellTypeConstants).Ent ireRow Set range2_for_the_OnlyData = Intersect(range2_for_the_OnlyData, Range("i:L")) range2_for_the_OnlyData.Copy Sheets("OnlyData").Range("j25").PasteSpecial xlPasteValues Application.CutCopyMode = False -------------------------------------------------------------------------- But this works only if I have data in columns i:L. If there are functions, etc then the above code does not work. How do I change the above code to ensure that ALL the data (in columns i:L) gets copied from the current sheet to the OnlyData sheet if there are any entries in column F of the current sheet. In other words, the results of all functions should be copied and not the functions themselves. Thanks. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
just copy the data, not the fuctions
Dim rng as Range
Dim rng1 as Range Dim range2_for_the_OnlyData as Range On Error Resume Next set rng = _ Columns("f").SpecialCells(xlCellTypeConstants).Ent ireRow set rng1 = _ Columns("f").SpecialCells(xlCellTypeFormulas).Enti reRow On Error goto 0 if not rng is nothing and not rng1 is nothing then Set range2_for_the_OnlyData = Union(rng,rng1) Elseif not rng is nothing then set range2_for_the_OnlyData = rng elseif not rng1 is nothing then set range2_for_the_OnlyData = rng1 End if if not range2_for_the_OnlyData is nothing then Set range2_for_the_OnlyData = Intersect(range2_for_the_OnlyData, Range("i:L")) range2_for_the_OnlyData.Copy Sheets("OnlyData").Range("j25").PasteSpecial xlPasteValues End If -- Regards, Tom Ogilvy lothario wrote in message ... Hi, In the following code the data in every row from columns i:L from the current sheet is copied to the OnlyData sheet if there are any entries in column F of the current sheet. ------------------------------------------------------------------------ Dim range2_for_the_OnlyData As Range Set range2_for_the_OnlyData = Columns("f").SpecialCells(xlCellTypeConstants).Ent ireRow Set range2_for_the_OnlyData = Intersect(range2_for_the_OnlyData, Range("i:L")) range2_for_the_OnlyData.Copy Sheets("OnlyData").Range("j25").PasteSpecial xlPasteValues Application.CutCopyMode = False -------------------------------------------------------------------------- But this works only if I have data in columns i:L. If there are functions, etc then the above code does not work. How do I change the above code to ensure that ALL the data (in columns i:L) gets copied from the current sheet to the OnlyData sheet if there are any entries in column F of the current sheet. In other words, the results of all functions should be copied and not the functions themselves. Thanks. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
just copy the data, not the fuctions
Thanks Tom. This worked.
One quick follow up question: How do I change your code so that this (conditional) search and copy begins in column F after row 7? Rows 1 to 6 should be completely ignored. (They contain titles.) Thanks. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
just copy the data, not the fuctions
Dim rng as Range
Dim rng1 as Range Dim rngF as Range Dim range2_for_the_OnlyData as Range set rngF = Range(cells(7,"F"),Cells(rows.count,"F").End(xlup) ) On Error Resume Next set rng = _ rngF.SpecialCells(xlCellTypeConstants).EntireRow set rng1 = _ rngF.SpecialCells(xlCellTypeFormulas).EntireRow On Error goto 0 if not rng is nothing and not rng1 is nothing then Set range2_for_the_OnlyData = Union(rng,rng1) Elseif not rng is nothing then set range2_for_the_OnlyData = rng elseif not rng1 is nothing then set range2_for_the_OnlyData = rng1 End if if not range2_for_the_OnlyData is nothing then Set range2_for_the_OnlyData = Intersect(range2_for_the_OnlyData, Range("i:L")) range2_for_the_OnlyData.Copy Sheets("OnlyData").Range("j25").PasteSpecial xlPasteValues End If -- Regards, Tom Ogilvy lothario wrote in message ... Thanks Tom. This worked. One quick follow up question: How do I change your code so that this (conditional) search and copy begins in column F after row 7? Rows 1 to 6 should be completely ignored. (They contain titles.) Thanks. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
just copy the data, not the fuctions
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use pivot data in formula or fuctions? | Excel Discussion (Misc queries) | |||
About Fuctions | Excel Worksheet Functions | |||
Creating Nested If Fuctions | Excel Worksheet Functions | |||
Using Worksheet Fuctions with Macros | Excel Discussion (Misc queries) | |||
Equalls fuctions | Excel Worksheet Functions |