Macro to open file, select a sheet and copy this to another fileq

[bearcub]

I have file where I want to open an existing file, select a sheet, copy this sheet and paste onto the sheet which is housing the macro.

This is what I have so far using the macro recorder. I've been searching online for something that is dynamic but a lot of the code I've seen doesn't allow me to select the destination worksheet or allow me to go to a destination of my choice.

I realize this is inefficient (since I'm using the recorder):

Sub PasteFalconData()

Dim wkbk As Workbook
Set wkbk = ThisWorkbook

    Application.FileDialog(msoFileDialogFilePicker).Show
    Workbooks(activework).Activate
    ActiveCell.Cells.Select
    Selection.Copy
    wkbk.Activate
    Sheets("Extract").ClearContents
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
[code]/

I want to open the open dialog box->select a file->open that file-> select and copy a sheet in that file->activate the file where the data is to be pasted ->close this file ->clear the destination sheet -> paste the data that was just copied from the file that was just opened (and close). I'm lost as to open the workbook that I've selected using the file picker (or show it be the folder picker). I using 2016 and the file I'm looking for will be either an xlsx file or a xlsb file (or maybe a CSV). I'm creating a template for our admins to use.

Thank you for your help.

Michael

 

[Trebor76]

Maybe stepping through each line of the code (by opening the macro and repeatedly pressing F8) will shed more light on what's happening.

 

[bearcub]

Yes, that is a good idea, I'll do that.

 

[wingsrul91]

How could i add/include code to paste as values?

Hi Michael,

This will open the selected file (if a file is selected). You don't say the name of the tab or its location in the workbook being imported so I've left that section blank for you fill in. The code also filters on Excel and CSV files:

Option Explicit
Sub PasteFalconData()

    Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Please select a file to open:"
        .Filters.Add "Excel and CSV files", "*.csv; *.xls; *.xls*", 1
        .Show
        On Error Resume Next 'In case the user has clicked the <cancel> button
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                Exit Sub 'Error has occurred so quit
            End If
        On Error GoTo 0
    End With
    
    Set wbImportWB = Workbooks.Open(strFullPath)
    'code here to copy and paste tab from Import WB into the current workbook
    
    'wbImportWB.Close False 'Close the Import WB without saving any changes. Uncomment when the code goes live.
    
End Sub

HTH

Robert

</cancel>

 

[default_name]

This should do everything you're after:

Option Explicit
Sub PasteFalconData()

    Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    Dim lngLastRow  As Long
    Dim lngLastCol  As Long
   
    Set wbThisWB = ThisWorkbook
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Please select a file to open:"
        .Filters.Add "Excel and CSV files", "*.csv; *.xls; *.xls*", 1
        .Show
        On Error Resume Next 'In case the user has clicked the  button
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                wbThisWB = Nothing
                Exit Sub 'Error has occurred so quit
            End If
        On Error GoTo 0
    End With
   
    Application.ScreenUpdating = False
   
    Set wbImportWB = Workbooks.Open(strFullPath)
    'code here to copy and paste tab from Import WB into the current workbook
    On Error Resume Next 'In case there's no data or tab doesn't exist
        With wbImportWB.Sheets("Extract")
            lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            If lngLastRow > 0 And lngLastCol > 0 Then
                'If the 'lngLastRow' and 'lngLastCol' variable have been set there's data to be copied.
                'The following copies the entire range from tab 'Extract' in the import workbook to cell A1 in 'Sheet1' of this workbook (change to suit).
                Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("Sheet1").Cells(1, 1)
            End If
        End With
    On Error GoTo 0
   
    wbImportWB.Close False 'Close the Import WB without saving any changes.
   
    Set wbThisWB = Nothing
    Set wbImportWB = Nothing
   
    Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

Thank you, Robert!
This is what I was looking for as well. Your code works GREAT...except for one thing.

However, just like the OP of this thread, is there a way to code this that "will allow me to select a sheet in the file I just opened"?

In the case of the original poster, the data is being pulled from a worksheet called "Extract" every time.
All of the workbooks I open have unique worksheet names.
Is there a way I can add on to your solution, but also include this feature (as initially requested by the OP).

Thanks in advance!

 

[Trebor76]

is there a way to code this that "will allow me to select a sheet in the file I just opened"?

I would say you need a form with a list box that gets populated with each sheet name within the workbook you're importing so you can select one or more to import.

As this thread is nearly four years old I suggest you open a new thread with a link back to this one if you think it will help provide you with a solution.

Thanks,

Robert

 

[default_name]

I would say you need a form with a list box that gets populated with each sheet name within the workbook you're importing so you can select one or more to import.

As this thread is nearly four years old I suggest you open a new thread with a link back to this one if you think it will help provide you with a solution.

Thanks,

Robert

Thanks Robert

I have created a new thread to try and address/solve this.
The post can be found here: VBA Import Workbook/Choose Sheet/Display Details