excel - Macro to pull data from closed workbook to another workbook -
i writing macro following:
every time open workbook, pull data closed workbook on computer , copy data sheet titled "availability" starting in cell a1.
currently, happens "true" put cell a1 on availability sheet.
please help.
sub openworkbooktopulldata() dim sht worksheet dim lastrow long lastrow = activesheet.usedrange.rows.count set sht = thisworkbook.worksheets(sheet1.name) dim path string path = "c:\users\" & environ$("username") & _ "\desktop\rc switch project\daily automation _ availability report.xlsx" dim currentwb workbook set currentwb = thisworkbook dim openwb workbook set openwb = workbooks.open(path) dim openws worksheet set openws = openwb.sheets("automation data") currentwb.sheets("availability").range("a1") _ = openws.range("a5:k" & lastrow).select openwb.close (false) end sub
as @greg mentioned, .select
not needed. once removed though, have new problem 2 ranges not same size. range("a1")
1 cell while other range @ least 11. current vba overwrite values in range called for, a1
here.
to around there 2 approaches work well.
resize
resize
left hand side same size right hand side.
sub openworkbooktopulldata() dim sht worksheet dim lastrow long lastrow = activesheet.usedrange.rows.count set sht = thisworkbook.worksheets(sheet1.name) dim path string path = "c:\users\" & environ$("username") & _ "\desktop\rc switch project\daily automation availability report.xlsx" dim currentwb workbook set currentwb = thisworkbook dim openwb workbook set openwb = workbooks.open(path) dim openws worksheet set openws = openwb.sheets("automation data") dim rng_data range set rng_data = openws.range("a5:k" & lastrow) currentwb.sheets("availability").range("a1").resize( _ rng_data.rows.count, rng_data.columns.count).value = rng_data.value openwb.close (false) end sub
copy/pastespecial
actually copy
, pastespecial
.
sub openworkbooktopulldata() dim sht worksheet dim lastrow long lastrow = activesheet.usedrange.rows.count set sht = thisworkbook.worksheets(sheet1.name) dim path string path = "c:\users\" & environ$("username") & _ "\desktop\rc switch project\daily automation availability report.xlsx" dim currentwb workbook set currentwb = thisworkbook dim openwb workbook set openwb = workbooks.open(path) dim openws worksheet set openws = openwb.sheets("automation data") dim rng_data range set rng_data = openws.range("a5:k" & lastrow) rng_data.copy currentwb.sheets("availability").range("a1").pastespecial xlpastevalues openwb.close (false) end sub
since looks going values anyways, use copy/pastespecial
route clarity in code.
Comments
Post a Comment