Use VBA to open file dialog and copy paste into current worksheet

[cgrulk]

Hello all,

I am attempting to write a VBA to input data into a sheet I am working on. I am trying to input data into a spreadsheet that is currently set up so I need to paste data into the range D2:D195 in the current sheet. I would like to create a button that would allow me to pick a file that would open up a file finder, I could select a file to import data from, and it would copy the correct range (coincidentally also D2:D195) and paste it into the current worksheet. Note: all the files I would be copying data from should be .csv files and will all have the same layout. I am trying to make this in a workbook that contains another sheet in which I would like to create a similar function, so if this macro could be easily applied on multiple sheets that would be ideal. I have tried recording a macro and working in other code to try to make it work, but I am a VBA novice and would really appreciate some help! Here is what I have so far.

Dim strFile As String
'Open the File Dialog

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    'Show the dialog box
    .Show
    'Store in fullpath variable
    fullpath = .SelectedItems.Item(1)
    
    
    'copy range from selected item into current worksheet
    Workbook (.SelectedItems.Item(1)), Range("D2:D195").Copy
    Windows("ThisSheet").Activate
    ActiveSheet.Range("D2:D195").Paste
End With

End Sub

 

[JLGWhiz]

Try this

Dim strFile As String, wb As Workbook
'Open the File Dialog
    With Application.FileDialog(msoFileDialogFilePicker)
         .AllowMultiSelect = False
         'Show the dialog box
         .Show
         'Store in fullpath variable
         fullpath = .SelectedItems.Item(1)
         'open the file
         Set wb = Workbooks(fullpath).Open
        'copy range from selected item into current worksheet
        wb.Sheets(1).Range("D2:D195").Copy
        Windows("ThisSheet").Activate
        ActiveSheet.Range("D2:D195").Paste
        wb.Close False
    End With
End Sub

 

[cgrulk]

I tried this and a subscript out of range error pops up and highlights this line

Set wb = Workbooks(fullpath).Open

 

[Fluff]

How about

Dim strFile As String, wb As Workbook
'Open the File Dialog
    With Application.FileDialog(3)
         .AllowMultiSelect = False
         'Show the dialog box
         If .Show Then
            'Store in fullpath variable
            fullpath = .SelectedItems.Item(1)
            
            'open the file
            Set wb = Workbooks.Open(fullpath)
         End If
         If wb Is Nothing Then Exit Sub
        'copy range from selected item into current worksheet
        wb.Sheets(1).Range("D2:D195").Copy
        Windows("ThisSheet").Activate
        ActiveSheet.Range("D2:D195").Paste
        wb.Close False
    End With

 

[cgrulk]

This resulted in the same error, but for a different line this time..

Windows("ThisSheet").Activate

I tried changing ("ThisSheet") to the name of the actual sheet, but this still yielded the same error. Thank you for your help!

 

[Fluff]

Change it to

ThisWorkbook.Worksheet("Sheetname").Activate

 

[cgrulk]

Change it to

ThisWorkbook.Worksheet("Sheetname").Activate

This resulted in a Compile Error "Method or data member not found"

 

[Fluff]

Oops it should be worksheets plural

 

[JLGWhiz]

Set wb = Workbooks(fullpath).Open

I don't believe I did that! Talk about senility,, wow!

 

[cgrulk]

I get a 438 run time error that the debugger highlights this line of code.

ActiveSheet.Range("D2:D195").Paste