Hi Jason,

This would be less complicated to create the formulas (vlookup), if you copy the
cells (values) from the B workbook instead of copy the whole sheet.
Assuming that the workbook A contains a sheet named Data, to paste the data to them
("Data" sheet may be hidden)

Sub test()
Dim Wk1 As Workbook, Wk2 As Workbook, fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add Description:="Excel Files", Extensions:="*.xls;*.xlsx"
.Show
On Error Resume Next
If Err.Number < 0 Then Err.Clear: Exit Sub
oFile = .SelectedItems(1)
End With

Set Wk1 = ThisWorkbook
Set Wk2 = Workbooks.Open(oFile)

'adapt range
Wk1.Sheets("Data").Range("A1:G20000").Value = Wk2.Sheets(1).Range("A1:G20000").Value

Wk2.Close SaveChanges:=False
Set fd = Nothing
End Sub

isabelle

Le 2017-02-10 Ã* 19:37, a écrit :
All,

I want to create a macro that first opens a dialog box where the user can
select a worksheet to open from their documents. To be more specific, I want
the information on that worksheet (there is only one spreadsheet on it) to be
added to the current worksheet where my macro and data file is located as a
new spreadsheet.

Once that is done I need to do a vlookup (or the macro version of one) that
looks for the part number in column "D" and goes to the "Item # Sheet" to
find the percentage based on the item number. Then in column G, take the
price in column "F" multiplied by the percentage the vlookup brought back.


Here is the vlookup:

D4 'Item # Sheet'!$A$2:$B$10221 2 FALSE

I tried this by doing a "record macro" and its not working.

Thanks ~ Jason