Importing one sheet from one file into another file in a different location

[bearcub]

I have a workbook where I need to import a worksheet to update my current workbook.

At this point, I don't know where this a source file is going to be located, what the sheet name in this source is going to be (it will vary depending upon the file user) and I also have to clear data from the destination sheet before i actually paste anything into it (in case there is more rows of old data than current data be added).

This file is going to used by our Admins to copy data from a data dump into another file. I'm presently using a binary file to reduce the file size so I think I would be looking for a Xlsb file.

I've google to try to find some generic code but they code that I've found seems to be specific to one's users particular situation.

These files might be stored on One Drive so they should be able to access this as well.

Thank you for your help.

Michael

 

[bearcub]

I was looking around at code tonight and found this one.

I realized after I send the original question that I need to open the source file to copy the sheet and paste into the destination file - I can't import it. So, I googled it and found this one.

It does look like this will work though I think I have to change the sheet name.

Do you think this pretty much captures what I want to do?

Sub CopyData()
    
    Dim fileDialog As fileDialog
    Dim strPathFile As String
    Dim strFileName As String
    Dim strPath As String
    Dim dialogTitle As String
    Dim wbSource As Workbook
    Dim rngToCopy As Range
    Dim rngRow As Range
    Dim rngDestin As Range
    Dim lngRowsCopied As Long
    
    
    dialogTitle = "Navigate to and select required file."
    Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
    With fileDialog
        .InitialFileName = "C:\Users\User\Documents" '<==Do I need to change this location? If ao, what do you suggest
        '.InitialFileName = ThisWorkbook.Path & "\" 'Alternative to previous line
       .AllowMultiSelect = False
        .Filters.Clear
        .Title = dialogTitle
        
        
        
        If .Show = False Then
            MsgBox "File not selected to import. Process Terminated"
            Exit Sub
        End If
        strPathFile = .SelectedItems(1)
    End With
     
    Set wbSource = Workbooks.Open(Filename:=strPathFile)
    Dim myRange As Range

    Set myRange = Application.InputBox(prompt:="Please select the cell you want to copy", Type:=8)
    Dim targetSheet As Worksheet
    Set targetSheet = wbSource.ActiveSheet
  
    'get the row of user select
   Set myRange = targetSheet.Range(targetSheet.Cells(myRange.Row, 1), targetSheet.Cells(myRange.Row, targetSheet.Columns.Count).End(xlToLeft))
    
    'copy data when there is an not empty cell in the range
    If WorksheetFunction.CountA(myRange) <> 0 Then
        Set rngDestin = ThisWorkbook.Sheets("Sheet1").Cells(1, "A")  ' Need to change this sheet name to something else, correct?
              
        myRange.SpecialCells(xlCellTypeVisible).Copy Destination:=rngDestin
    End If

    wbSource.Close SaveChanges:=False
    
    Set fileDialog = Nothing
    Set rngRow = Nothing
    Set rngToCopy = Nothing
    Set wbSource = Nothing
    Set rngDestin = Nothing
    
    'MsgBox "The data is copied"

End Sub
[code]/

Thank you for your help,

Michael