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
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