just copy the data, not the fuctions

  • Thread starter Thread starter lothario
  • Start date Start date
L

lothario

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).EntireRow
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.
 
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).EntireRow
set rng1 = _
Columns("f").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
 
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.
 
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
 
Back
Top