Vlookup using external workbook as Table Array

haybie

New Member
Joined
Mar 5, 2014
Messages
4
Hello. I have got incentive reports and every month we get spread sheet of data. I need to Vlookup certain datas e.g checking if there is any new users in the report. All the master files have the same title of "3 digit number" INCENTIVE REPORT. I have come up with this formula the highlight in bold is the Vlookup part: Sub Username_Check()

Dim LR As Integer
LR = Range("C" & Rows.Count).End(xlUp).Row

Range("C2:C10000").Select

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

On Error Resume Next

SheetName = InputBox("Specify a filename", "Filename")
If SheetName = "" Then Exit Sub

Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select



ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & SheetName & " INCENTIVE REPORT.xlsx]user summary'!R7C4:R1000C4,1,FALSE)"
Range("D2").Select

ActiveWorkbook.UpdateLink Name:=[" & SheetName & " INCENTIVE REPORT.xlsx], Type:=xlExcelLinks doesn't seem to do anything

Selection.AutoFill Destination:=Range("D2:D" & LR)

Range("D1").Select
Range("D1:D" & LR).Select

Selection.AutoFilter
ActiveSheet.Range("D1:D" & LR).AutoFilter Field:=1, Criteria1:="#N/A"

'Columns("D:D").Select
'Selection.EntireColumn.Hidden = True



End Sub. The problem that is occurring is that it doesn't automatically find the file. I have to still update the values and select the files twice. It is on a sharing drive S:\Sharingfolder\Programfolder\Program. Is there to amend it so it does it automatically? Any help will much appreciated.
 

Forum statistics

Threads
1,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top