Import data from an excel workbook to another workbook without manually selecting

Hi all,

I'm trying to write a VBA code in my worksheet ( blank excel sheet) to pull data from another excel sheet without opening that file.

This code will open a new window to open my file and range. But, I'm trying to avoid the process of going through different windows to select my data.

I need this file to pull data from my C:\ directory with the range that I want. I use active X button for my convenience.

Private Sub CommandButton2_Click()
 Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
     
    Dim rngSourceRange As Range
    Dim rngDestination As Range
     
    Set wkbCrntWorkBook = ActiveWorkbook
     
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2002-03", "*.xls", 1
        .Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
        .Filters.Add "Excel 2010", "*.xlsx; *.xlsm; *.xlsa", 2
        .AllowMultiSelect = False
        .Show
         
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
        End If
    End With
End Sub

Yes, I want to pull it from Sheet1 in the Excel.xlsx Workbook

Thank you for this post. But, this still opens a message box for me to select the range and my destination. I want it to select certain range ex: A1:E10 and paste it automatically in my worksheet. So, basically all the message boxes should be automated.

Thank you so much It works!!