Copying Excel range to another sheet

[rspai]

Code: Select all

Xl := ComObjActive("Excel.Application")
filename:= "D:\Selenium_Excel\FI_Data.xlsx"
wbk:=XL.Workbooks.Open(filename)
dws2:=wbk.sheets("BMData")
tws:=wbk.Sheets("tempData")
xlDown := -4121
xlUp := -4162
tws.Select
copyrange:= tws.Range("B1", tws.Range("B1").End(xlDown))
dws2.Range("a1"):= copyrange.value
wbk.Save()

A workbook has two sheets "BMData" and "tempData". Tempdata has values in Column B but the count of rows changes. Hence, I have used the End(xlDown) code above. However, in the destination sheet (dws2/"BMData") only the value in B1 is copied to a1 (ie only one value is copied. Currently column B in tempData sheet has 13 values in 13 rows).

[flyingDman]

try:

Code: Select all

Xl := ComObjActive("Excel.Application")
sht1:=xl.sheets("sheet1")
sht2:=xl.sheets("sheet2")
lstrw := sht2.range("B1").End(-4121).row
sht1.Range("A1:A" lstrw) := sht2.Range("B1:B" lstrw).value

or (using the clipboard)

Code: Select all

Xl := ComObjActive("Excel.Application")
sht1:=xl.sheets("sheet1")
sht2:=xl.sheets("sheet2")
lstrw := sht2.range("B1").End(-4121).row
sht2.Range("B1:B" lstrw).Copy(sht1.Range("A1"))