code to get data from another workbook selecting sheet

I need to copy and paste data from multiple workbooks and multiple sheets. (name keeps changing of worksheets as well as workbook)

I have a code but here I need to manually select the cells. I just want to select the sheet and it should automatically import the entire data in the sheet to a designated worksheet in my workbook. Is that possible?

Sub ImportDatafromotherworksheet()
    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 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .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

2 answers

  • answered 2018-10-16 19:16 Mike - SMT

    Here is a simple example of you can copy from one to the other. Let me know if you need help on something more specific.

    Sub testing()
    
        Dim des_wb As Workbook, sou_wb As Workbook
        Dim des_ws As Worksheet
        Dim sou_rng As Range
    
        ChDir (ActiveWorkbook.Path)
        Set des_wb = ActiveWorkbook
    
        Set sou_wb = Workbooks.Open(Application.GetOpenFilename)
        Set sou_ws = sou_wb.Worksheets(1)
    
        Set sou_rng = sou_ws.Range("A1").CurrentRegion
    
        sou_rng.Copy
        des_wb.Worksheets(1).Range("A1").PasteSpecial
    
    End Sub
    

  • answered 2018-10-17 06:50 DisplayName

    you can use "source" range to get its Parent sheet UsedRange, as follows:

    Sub ImportDatafromotherworksheet()
        Dim wkbCrntWorkBook As Workbook
        Dim rngSourceRange As Range
        Dim rngDestination As Range
    
        Set wkbCrntWorkBook = ActiveWorkbook
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                With Workbooks.Open(.SelectedItems(1)) ' open and reference current selected "source" workbook
                    Set rngSourceRange = Application.InputBox(prompt:="Select any cell in the wanted sheet ", Title:="Source sheet chosing", Default:="A1", Type:=8) ' have user select any cell in source sheet
                    If Not rngSourceRange Is Nothing Then ' if any valid "source" range selected
                        wkbCrntWorkBook.Activate ' this could be omitted since subsequent InputBox allowa user to switch between open workbooks
                        Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8) ' have user select "destination" cell
                        If Not rngDestination Is Nothing Then ' if any valid "destination" range selected
                            rngSourceRange.Parent.UsedRange.Copy rngDestination.Cells(1, 1) ' be sure to collapse "destination" range to a single cell
                            rngDestination.CurrentRegion.EntireColumn.AutoFit
                        End If
                    End If
                    .Close False ' close referenced (i.e. current "source" sheet) sheet
                End With
            End If
        End With
    End Sub