Copy only value and not formula

Question

I got a sheet named Sheet1 which contains data manipulated through formulas. I need a specific range of data, say for example, A2 to D63 copied in a new sheet i.e. Sheet2 only by value.

The range of data in Sheet1 is dynamic i.e. it changes every time so I need that macro to handle this.

Answer 1

try

Worksheets("Sheet1").Range("D63").value = .Range("A2").End(xlDown).Offset(0, 41).value

I'm not very good at VBA so please excuse any errors

Source: http://www.mrexcel.com/forum/excel-questions/49124-visual-basic-applications-copy-paste-cell-value-only-not-formula.html

Answer 2

I was a bit off the first time around, providing some updated code here that should hopefully look more like what's expected. Reading back the whole thread it sounds not only the data, but the range itself may be dynamic here. If so is there a pattern in the way the range actually reshapes?

The below code adds 3 rows to the source range every time the source data changes - should be written to the "sheet1" worksheet module

Private Sub Worksheet_Change(ByVal Target As range)  
    Static r As range
    If r Is Nothing Then   
        Set r = Worksheets("Sheet1").range("A2:D63")  
        Else  
        Set r = Union(r, Worksheets("Sheet1").range(Cells(r.Row + r.Rows.Count, r.Column), _
        Cells(r.Row + r.Rows.Count + 2, r.Column + r.Columns.Count - 1)))    
    End If
    r.select
    If Not Application.Intersect(Target, r) Is Nothing Then  
        For Each Cell In Application.Intersect(Target, r)  
            Worksheets("Sheet2").Cells(Cell.Row, Cell.Column).Value = Cell.Value  
        Next Cell  
    End If 

End Sub