how to extract data on workbook loaded in listbox userform?

jns1087

New Member
Joined
May 3, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
is there a way to extract data on workbook loaded in listbox userform? ive been struggling to do this. as display on listbox 2, i have 3 workbooks loaded. and i have a process button at the middle which i want to copy a range of cell data from the three workbooks at listbox2, and then paste data to my current workbook sheets. ive been doing this for my studying. thank you
 

Attachments

  • Capture.PNG
    Capture.PNG
    39 KB · Views: 14

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jns1087

New Member
Joined
May 3, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Which range and from which sheet?


Paste in which cell?
on workbook AGEFEP, FEUS & REMDUS the data are in B10:K10
and then going to paste it to current workbook with sheet name AGEFEP, FEUS, REMDUS E53:L53

thanks,
 

jns1087

New Member
Joined
May 3, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Which range and from which sheet?


Paste in which cell?
forgot to mention that each workbook FEUS,AGEFEP,REMDUS has a default Sheet1 for its data. and the current workbook has a sheetname based on loaded workbook (AGEFEP,FEUS,REMDUS)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Try this.
Set commandbutton1, textbox1 for the name of your controls.

VBA Code:
Private Sub CommandButton1_Click()
  Dim i As Long, tBox1 As String, sName As String
  Dim wb2 As Workbook
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  tBox1 = TextBox1.Value
  If tBox1 = "" Then
    MsgBox "Fill foder"
    Exit Sub
  End If
  If Right(tBox1, 1) <> "\" Then tBox1 = tBox1 & "\"
  If Dir(tBox1, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  
  With ListBox2
    For i = 0 To ListBox2.ListCount - 1
      If .Selected(i) Then
        sName = Left(.List(i), InStrRev(.List(i), ".") - 1)
        If Dir(tBox1 & .List(i)) <> "" Then
          Set wb2 = Workbooks.Open(tBox1 & .List(i))
          wb2.Sheets(1).Range("B10:K10").Copy
          ThisWorkbook.Sheets(sName).Range("E53").PasteSpecial xlPasteValues
          wb2.Close False
        End If
      End If
    Next
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,623
Members
417,101
Latest member
amoverton2

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
Top