Macro To Browse Select File And Import Specific Data

[violetpedal]

I'm helping my boss with a payroll calculation worksheet. All of the calculations has been created and I just need a few Macros to do the following:

1) Open directory C:\ to allow me to select an invoice file
2) Once the Invoice has been selected (see attached sample), I need the data from cell A3:J29 to be copied to the destination workbook in sheet Calculation, cell A2-J28.
3) The next Macro would copy data from cell A3:J29 in Invoice file and paste it to cell L2-U28 in the Calculation worksheet. The same will follow for the the rest of the tables.

I'd really really appreciate your help. It always take my boss a good couple of hours to do the payroll calculation and I just wanted to help.

THANK YOU!!!

[Admin]

Hi,

Welcome to ExcelFox !!

Try this code. The code will allow you to select the invoice file as well as the destination range. So don't need multiple macros.

Sub kTest()
    
    Dim SourceFile  As String
    Dim Dest        As Range
    Dim wbkSource   As Workbook
    Dim SourceData
    
    Const SourceRange = "A3:J29"
    
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .AllowMultiSelect = False
        If .Show = -1 Then
            SourceFile = .SelectedItems(1)
        End If
    End With
    
    If Len(SourceFile) Then
        Set wbkSource = Workbooks.Open(SourceFile)
        SourceData = wbkSource.Worksheets("Contractors").Range(SourceRange).Value2
        wbkSource.Close 0
        Set wbkSource = Nothing
        
        Set Dest = Application.InputBox("Select the destination range", "Destination", "A2", Type:=8)
        
        Dest.Cells(1).Resize(UBound(SourceData, 1), UBound(SourceData, 2)) = SourceData
        
    End If
    
End Sub

[violetpedal]

THANK YOUUU SO MUCH!
It works perfectly!!!!

[violetpedal]

Hello,

I'm having some trouble with these spreadsheets again. Now different Invoices have different formats, therefore I can't use the standard data range (A2:A28) anymore. Can you please make some changes to the macro so I can select the data from the Invoice spreadsheet?

Thank you a zillions!! It has helped us so much.

[Admin]

Hi

Sub kTest()
    
    Dim SourceFile  As String
    Dim Dest        As Range
    Dim wbkSource   As Workbook
    Dim SourceRange As Range
    Dim SourceData
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .AllowMultiSelect = False
        If .Show = -1 Then
            SourceFile = .SelectedItems(1)
        End If
    End With
    
    If Len(SourceFile) Then
        Set wbkSource = Workbooks.Open(SourceFile)
        Set SourceRange = Application.InputBox("Select the source range", "Destination", "A2", Type:=8)
        SourceData = SourceRange.Value2
        wbkSource.Close 0
        Set wbkSource = Nothing
        
        Set Dest = Application.InputBox("Select the destination range", "Destination", "A2", Type:=8)
        
        Dest.Cells(1).Resize(UBound(SourceData, 1), UBound(SourceData, 2)) = SourceData
        
    End If
    
End Sub