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