1 year ago
#387860
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