Help with VLOOKUP from external file pasting into existing file

MrRetard

New Member
Joined
Nov 2, 2017
Messages
1
I have a macro that combines three excel files into one workbook (lets call it WORKBOOKCOMBINED), and Sheet 1 of this new, unsaved file becomes the combined data of the 3 (so Sheet 1= "Combined", Sheets 2-4 are one of the copied sheets (Sheet2 is File A data, Sheet 3 is File B data, Sheet 4 is File C data). Sheet 1 ("Combined") contains all the data from Sheets 2-4 (no sorting)

When I finish my macro, it leaves me on the newly created (not yet saved) Workbook's Worksheet 4, with all the data selected. (Is this important to note?)

From here, I want to run a macro that will perform a VLOOKUP of WORKBOOKCOMBINED Column A (starting at A2) from an external file (which I want to be able to choose at my discretion), and have that selected Workbooks Sheet1 (which also is named "Combined") Column J be inserted into WORKBOOKCOMBINED's Sheet1 ("Combined") column K

This is what I have so far and it doesn't do anything or work. I'm lost
Sub OldData()

Sheets("Combined").Select

Dim rw As Long
Dim x As Range
Dim extwbk As Workbook
Dim twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("X:\FilePath")
Set x = extwbk.Worksheets("Combined").Range("A2:J1000")

With twb.Sheets("Combined")
For rw = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
.Cells(rw, 11) = Application.VLookup(.Cells(rw, 1).Value2, x, 10, False)
Next rw
End With
extwbk.Close savechanges:=False

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,215,475
Messages
6,125,028
Members
449,205
Latest member
Eggy66

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top