Popup box to select open workbook and copy/paste from it

Malorie

New Member
Joined
Mar 2, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I’m sorry, I try myself at VBA coding but still not very good at it ?

So I have some macros that work, but before running them I need to import data into the workbook my macros are in.

So I want to have one workbook open with the macros (let’s call it code workbook), and then the source is a CSV workbook (1 column, x lines) which should also be open.

Basically I want to be able to click on a button in the first sheet of the Code Workbook, and then have a popup box listing all open workbooks for me to choose from.

Then when I chose the Source Workbook from the popup box, I would like the data of the first sheet of the Source Workbook (which always has a different name, because it’s extracted from another software) to be copied and pasted in another sheet of my Code Workbook.

Then I’ll figure out a way to run the macros I do have.

Is this something difficult ? I don’t know where to start …
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
hi malorie,
Welcome to the board.

For your query
Basically I want to be able to click on a button in the first sheet of the Code Workbook, and then have a popup box listing all open workbooks for me to choose from.

Then when I chose the Source Workbook from the popup box, I would like the data of the first sheet of the Source Workbook (which always has a different name, because it’s extracted from another software) to be copied and pasted in another sheet of my Code Workbook.
try following 2 codes inside a userform having a listbox (listbox1)
VBA Code:
Private Sub UserForm_Initialize()
Dim wk As Workbook
Dim ws As Worksheet

For Each wk In Application.Workbooks
    ListBox1.AddItem wk.Name
Next
I = ListBox1.ListCount


If I = 1 Then
    ListBox1.Clear
    ListBox1.AddItem "Only Master File is activated. Please Open Source file and retry again."
    ListBox1.Enabled = False
    Exit Sub
End If

End Sub


VBA Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim I As Integer

On Error Resume Next
I = ListBox1.ListIndex
I = I + 1

If Workbooks(I).Name = ThisWorkbook.Name Then GoTo trap:


Workbooks(I).Sheets(1).UsedRange.Copy

ThisWorkbook.Sheets(2).Range("a1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Unload Me
Exit Sub

trap:
MsgBox "Ooops... Looks like you have selected master file." & vbNewLine & _
        "Please select another file...!", vbRetryCancel
Exit Sub

End Sub

sample file is available here....

hth....
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top