excel - xlDown and xlToRight results embedded into Sheet.Range(cells(#,#),cells(#,#) -

i have been using these forums guest while , have not had ask yet. been able find or find , modify suit needs. alas, beyond me..

the following code works:

private sub commandbutton1_click() set appxl = createobject("excel.application") dim myfile window dim currentsheet worksheet dim lastrow double dim lastcol double dim sourcefilename string '-------------------------------- 'get eprofiling bulk report data '-------------------------------- sourcefilename = "eprofiling.xlsx"  'open source file.xlsx appxl     .workbooks.open activeworkbook.path & "\" & sourcefilename     .visible = false end  'get eprofiling progress percentage set myfile = appxl.windows(sourcefilename) myfile.activate set currentsheet = appxl.sheets(2)  'past table in report builder lastrow = currentsheet.range("b1").end(xldown).row sheets("ep_80%_date").range("b1:ff" & lastrow).clearcontents sheets("ep_80%_date").range("b1:ff" & lastrow) = currentsheet.range("a1:ff" & lastrow).value appxl.workbooks(sourcefilename).close  msgbox ("complete") 

however, when try following:

'past table in report builder lastrow = currentsheet.range("b1").end(xldown).row lastcol = currentsheet.range("b1").end(xltoright).column sheets("ep_%").range(cells(1, 2), cells(lastrow, lastcol)).clearcontents sheets("ep_%").range(cells(1, 2), cells(lastrow, lastcol)) = currentsheet.range(cells(1, 2), cells(lastrow, lastcol)).value 

i receive "run-time error '1004': application-defined or object-defined error"

i trying use port 4 workbooks (8 sheets) can not change or edit due government department data supplied from.

historically had referred directly cells in appropriate external workbooks. becomes major problem when peanut decided add or move column or row of date. al least way, know 'live' data when user presses command button , formulas , pivot tables not reading 'formulas' reading true cell data....if makes sense.... i'm tired...