1 year ago

#387860

test-img

Anthony L

How can I paste ranges using variable references?

I am attempting to paste several ranges from one workbook to another using variables as the references. I was successful in running this for individual cells, but I think my syntax is wrong now that I am trying ranges. I am fairly new to VBA, please let me know your thoughts if this is an easy catch for you. Thanks!

Sub pasteRanges()

     x1 = Sheets("Sheet1").Cells(19, 14).value
     x2 = Sheets("Sheet1").Cells(23, 14).value
     x3 = Sheets("Sheet1").Cells(26, 14).value
     x4 = Sheets("Sheet1").Cells(30, 14).value
     x5 = Sheets("Sheet1").Cells(33, 14).value
     x6 = Sheets("Sheet1").Cells(34, 14).value
     x7 = Sheets("Sheet1").Cells(38, 14).value
     x8 = Sheets("Sheet1").Cells(42, 14).value
     x9 = Sheets("Sheet1").Cells(44, 14).value
     x10 = Sheets("Sheet1").Cells(57, 14).value

     y = Sheets("Sheet1").Cells(19, 15).value

     Z = Sheets("Sheet1").Cells(19, 16).value


     rng1 = Sheets("Sheet1").Range(".Cells(x1, y), .Cells(x2, y)")
     rng2 = Sheets("Sheet1").Range(".Cells(x3, y), .Cells(x4, y)")
     rng3 = Sheets("Sheet1").Range(".Cells(x5, y), .Cells(x6, y)")
     rng4 = Sheets("Sheet1").Range(".Cells(x1, Z), .Cells(x2, Z)")
     rng5 = Sheets("Sheet1").Range(".Cells(x3, Z), .Cells(x4, Z)")
     rng6 = Sheets("Sheet1").Range(".Cells(x5, Z), .Cells(x6, Z)")
     rng7 = Sheets("Sheet1").Range(".Cells(x7, Z), .Cells(x8, Z)")
     rng8 = Sheets("Sheet1").Range(".Cells(x9, Z), .Cells(x10, Z)")


     Workbooks.Open ("Book2")

        
        
     Application.Workbooks("Book2.xls,").Worksheets("Destination").Range("rng1") _
    = Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("H19:H23")

 End Sub

excel

vba

copy

range

paste

0 Answers

Your Answer

Accepted video resources