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

 

[Fluff]

Oops, it should be PasteSpecial

 

[cgrulk]

Got it to work! Last question.. When I run this a window pops up asking if I would like to share this info to the clipboard. Is there a way to automatically select yes or no for this so it does not pop up each time? BTW this is the version of code that finally got working.

Sub InData()
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("C2:C195").Copy
        ThisWorkbook.Worksheets("SheetName").Activate
        ActiveSheet.Range("D2:D195").PasteSpecial
        wb.Close False
    End With
End Sub

 

[Fluff]

Just add this line

       ActiveSheet.Range("D2:D195").PasteSpecial
      application.cutcopymode=False 
      wb.Close False

 

[cgrulk]

Thank you! Everything works now.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[JLGWhiz]

Deleted