setting a range on page = range on another page using .Value

Question

I'm trying to avoid using copy and Paste in VBA in order to speed up my code. I've got a range on worksheet that I want to populate with the values from another sheet in the same workbook.

Ideally, I'm trying to accomplish range(x,Y).value = sheets("sheet2").range(a,b).value

both the matrices I'm defining are the same size (4x12) in case that would be an issue...

Dim App_dates As Integer

App_dates = Sheets("Pipeline - Underwriting Data D").Range("G1").End(xlDown).Row - 1


Dim UW As Integer
UW = Range("UW_cell1").Row



'Set value = value

Range("UW_cell1", "UW_btm_end").Value = Sheets("Pipeline - Underwriting Data D").Range("A2", Cells(App_dates + 1, 12)).Value

I'm getting Run-time error '1004': application-defined or object defined error.

Any suggestions?

Answer 1

if named range "UW_cell1" is Workbook scoped, or if it's in the currently active sheet, then you can use:

With Worksheets("Pipeline - Underwriting Data D")
    With .Range("A2", .Cells(App_dates + 1, 12))
        Range("UW_cell1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End With

otherwise

With Worksheets("Pipeline - Underwriting Data D")
    With .Range("A2", .Cells(App_dates + 1, 12))
        Worksheets("myTargetSheetName").Range("UW_cell1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End With

where you have to change "myTargetSheetName" to your actual target sheet name