Help with Ref! when displaying values

[dhotchin1986]

Hi All,

This is driving me mad. Maybe i'm not looking at this right. I have a macro that finds data from a range from all worksheets and displays them on a summary sheet. This works great. However, if the cell its copying has a formula then it just shows Ref!

Is there away to display or to copy the cell value? It displays plain text OK.

Here is my code. The macro runs when click on a button.

Sub GrabData()
    Dim wsComb As Worksheet
    Dim wsSrc As Worksheet
    Dim LastRowSrc As Long
    Dim LastRowDst As Long
         
    Set wsComb = Worksheets("Summery")
    LastRowDst = 1
    
    For Each wsSrc In Worksheets
        
        
        If wsSrc.Name <> wsComb.Name Then
            
            LastRowSrc = wsSrc.Range("A" & Rows.Count).End(xlUp).Row
            
            wsSrc.Range("DK1:DO" & LastRowSrc).Copy wsComb.Range("A" & LastRowDst)
                        
            LastRowDst = LastRowDst + LastRowSrc
            
        End If
    
    Next wsSrc
     
End Sub

 

[mikerickson]

If there are cells in DK1:DOx that have relative references that refer to cells to the left, then there will be #REF errors generated when moving those formulas.

If you want to move just the Values

With wsSrc.Range("DK1:DO" & LastRowSrc)
    wsComb.Range("A" & LastRowDst).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[dhotchin1986]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Each worksheet is set out the same, but the values I need are dashed all over the worksheet.

So all I have done is setup a =B1 (example formula) to display all the results I am looking for on the same worksheet (duplicate the cells) to display in a column structure instead of the user friendly structure the user sees.

I then use the macro to grab the ‘new range of cells’ and list them all on a summary page.

Is there a better way then what I am doing now. Maybe I’m looking at this the wrong way.


Thank you.

Darren

 

[mikerickson]

You said in the OP, that it all works great except when the source cell contains a formula.
Instead of using .Copy and Paste, just set the Value of the destination range to the Value of the source cell.

Dim SomeRange as Range, OtherRange As Range

' Do Not Do This
SomeRange.Copy Destination:=OtherRange

' YES, do this
OtherRange.Value = SomeRange.Value

The only thing to watch for in switching from copy/paste to setting .Value is that the size of the ranges must be the same. To continue the example from above

' Sure Fire approach
With SomeRange
    OtherRange.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With