Pasting range across workbooks-CodePudding

I am trying to copy/paste a range to another workbook. I am using variables to define the cells/ranges because the destination range changes on a daily basis. This method worked for pasting cells only, but I am getting application-defined or object-defined error error now that I am trying it with range. The error is in the last two lines, and is certainly deriving from the range stored as "a". The line runs perfectly when I use hard coded ranges, but the variable is throwing it off. Here's my code: Any thoughts?

Sub pasteVariableRange()
      Dim x1 As Integer
      Dim x2 As Integer
      Dim y As Integer
      x1 = Sheets("Source").Cells(19, 14).value
      x2 = Sheets("Source").Cells(23, 14).value
      y = Sheets("Source").Cells(19, 15).value

      Workbooks.Open ("Book2".xlsx")
      Worksheets("Destination").Activate
        Dim a As Range
        Set a = Range(Cells(x1, y), Cells(x2, y))
      
    Application.Workbooks("Book2.xlsx").Worksheets("Destination").Range("a") _
    = Application.Workbooks("Book1.xlsm").Worksheets("Source").Range(".Cells(19, 8), .Cells(23, 8)")
 End Sub

CodePudding user response：

Application.Workbooks("Book2.xlsx").Worksheets("Destination").Range("a") _
= Application.Workbooks("Book1.xlsm").Worksheets("Source").Range(".Cells(19, 8), .Cells(23, 8)")

This might be easier to follow.

Dim src_sh as Sheet
Dim des_sh as Sheet

Set src_sh = Application.Workbooks("Book1.xlsm").Worksheets("Source")
Set des_sh = Application.Workbooks("Book2.xlsx").Worksheets("Destination")
des_sh.Range(a) = src_sh.Range(src_sh.Cells(19, 8), src_sh.Cells(23,8))

CodePudding user response：

Should be close:

Sub pasteVariableRange()
    
    Dim x1 As Long, x2 As Long, y As Long 'prefer Long over Integer
    Dim wb As Workbook, a As Range, wsSrc As Worksheet
    
    Set wsSrc = ThisWorkbook.Worksheets("Source")
    With wsSrc
        x1 = .Cells(19, 14).Value
        x2 = .Cells(23, 14).Value
        y = .Cells(19, 15).Value
        Set a = .Range(.Cells(x1, y), .Cells(x2, y))
    End With
    
    Set wb = Workbooks.Open("Book2.xlsx")
    wb.Worksheets("Destination").Range(a.Address) = a.Value
    wb.Close True
    
End Sub