excel - VBA - on click - generate a prompt to confirm which workbook / spreadsheet to look at -

i know how select data different workbook / sheet i'm little clueless how go generating vba question box prompt user on click input relevant workbook / sheet.

for example:

workbook 1, sheet 1 needs data

workbook 2, sheet 2 has data

workbook 1 contain button called 'get data'.

on click - 'get data' button prompt user input workbook , subsequently worksheet copy from.

once user specifies workbook 2, sheet 2, range copy selected sheet/book same (regardless of sheet ref) data collected workbook 2, sheet 2 , pasted workbook 1, sheet 1.

• workbook 1, sheet 1 used paste data into.

i hope haven't explained in confusing manner, i've been going on in head while, if needs clarity, please shout!

many thanks.

i think best way create userform

with 2*2 comboboxes (1 workbooks (here cb_wb) , 1 sheets (here cb_ws), 2 times : source , destination)

with code :

private sub cb_wb_change() me.cb_ws.clear on error resume next each ws in workbooks(me.cb_wb.value).worksheets     me.cb_ws.additem ws.name next ws end sub  private sub userform_initialize()  each wb in application.workbooks     me.cb_wb.additem wb.name next wb  end sub