excel - VBA Macro workbook.open or workbook.activate through variable reference -

excel - VBA Macro workbook.open or workbook.activate through variable reference -

how reference primary workbook , sec workbook open through sub procedure? effort workbooks.("client_path").activate goal macro open separate workbook, assigned variable client_path , reconcile every (1 200) values in column a:a values of column k:k of primary workbook. if value found on client_path workbook (again column a:a), not on primary workbook (again column k:k) - add together unique value column m:m of primary workbook. opposite logic, value found on primary workbook not found on client_path workbook appear in column n:n of primary workbook.

the name of primary workbook developing code title "client dirty watchlist" contents of workbook client_path update daily , useless time passes.

do need create function accomplish variable workbook reference?

sub client_dirty_recon() dim client_path string dim client_watchlist workbook dim client_client_email workbook set client_watchlist = activeworkbook dim email_range range dim watchlist_range range application.screenupdatclient = false client_path = range("path") workbooks.open client_path dim recon_list range 'for each n in recon_list: dim variant = 1 200 set email_range = activeworkbook.activesheet.range("a" & i) dim b variant each b in email_range set watchlist_range = sheets("client dirty watchlist").range("b:b") 'if b next b next end sub

can create references workbook earlier?

dim wb workbook dim wbdirty workbook set wb = thisworkbook set wbdirty = workbooks.open client_path

then when define ranges, excel knows workbook belong to.

dim rngreconcile range dim rngwatch range set rngreconcile = wb.sheets(1).range("k:k") set rngwatch = wbdirty.sheets("client dirty watchlist").range("b:b")

then go on on looping code

excel vba excel-vba excel-formula