Search code examples
excelvbaonclickcopyprompt

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

I am fairly certain I know how to select data from a different workbook / sheet but I'm a little clueless as to how I would go about generating a VBA question box that would prompt the user on click to input the relevant workbook / sheet.

For example:

Workbook 1, sheet 1 needs data

Workbook 2, sheet 2 has data

Workbook 1 would contain a button called 'get data'.

On click - 'Get Data' button would prompt the user to input the workbook and subsequently the worksheet to copy from.

Once the user specifies workbook 2, sheet 2, the range to copy from the selected sheet/book would always be the same (regardless of the sheet ref) so the data would be collected from workbook 2, sheet 2 and would then be pasted back into workbook 1, sheet 1.

  • Workbook 1, sheet 1 will always be used to paste data into.

I hope I haven't explained this in too confusing a manner, I've been going over this in my head for a while, so if anyone needs clarity, then please shout!

Many thanks.

Solution

  • I think that the best way would be to create a UserForm

    with 2*2 comboboxes (1 for workbooks (here Cb_Wb) and one for sheets (here Cb_Ws), 2 times : source and destination)

    With that code :

    Private Sub Cb_Wb_Change()
Me.Cb_Ws.Clear
On Error Resume Next
For Each ws In Workbooks(Me.Cb_Wb.Value).Worksheets
    Me.Cb_Ws.AddItem ws.Name
Next ws
End Sub

Private Sub UserForm_Initialize()

For Each wb In Application.Workbooks
    Me.Cb_Wb.AddItem wb.Name
Next wb

End Sub