VLookup

[waxb18]

Hi having problems with this macro

At the moment it is copying down the formula to cell G5000 but this is a variable sometimes it can be G2000 sometime G10000,

Can someone show me how to edit this so no matter what it pastes down to the last cell of data

Thanks in advance

Sub VlookupRM()
'
' VlookupRM Macro
'
'
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[All Engineers.xls]Eng List'!C1:C4,3,FALSE)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G5000")
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[All Engineers.xls]Eng List'!C1:C4,4,FALSE)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H:H")
Range("H:H").Select
ChDir _
"G:\Stats"
End Sub

 

[VoG]

What is the code for the subs Open_Extract_and_COPYANDPASTEDATA and VoG

 

[waxb18]

Sub Open_Extract_and_COPYANDPASTEDATA()

Application.DisplayAlerts = False
Workbooks.Open Filename:= _
"G:\Business Stats\EAGA Reporting\DSO Job Extract\Outbound Call Data Extract\DSO Outbound Call Data Extract.XLS"
Application.DisplayAlerts = True

myworkbook = "DSO Outbound Call Data Extract 140611 MACRO TEMPLATE.xls"

Windows(myworkbook).Activate
Range("A1", Range("H1").End(xlDown)).Select
Selection.ClearContents

Extract = "DSO Outbound Call Data Extract.XLS"
Windows(Extract).Activate


Range("A1", Range("H1").End(xlDown)).Copy

Windows(myworkbook).Activate
Range("A1", Range("H1").End(xlDown)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Sub VoG()
Bdir = "G:\Business Stats\All Engineers.xls"
Workbooks.Open (Bdir)
myworkbook = "DSO Outbound Call Data Extract 140611 MACRO TEMPLATE.xls"
Windows(myworkbook).Activate
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("F2:F" & LR).FormulaR1C1 = "=VLOOKUP(RC[-1],'[All Engineers.xls]Eng List'!C1:C4,2,FALSE)"
Range("G2:G" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],'[All Engineers.xls]Eng List'!C1:C4,3,FALSE)"
Range("H2:H" & LR).FormulaR1C1 = "=VLOOKUP(RC[-3],'[All Engineers.xls]Eng List'!C1:C4,4,FALSE)"
ActiveWorkbook.SaveAs Filename:="DSO Outbound Call Data Extract 140611 MACRO TEMPLATE " & Format(Date, "dd-mm-yyyy") & ".xls"
Workbooks("Engineers.xls").Close
End Sub
Sub polo()
Open_Extract_and_COPYANDPASTEDATA
VoG
End Sub


This is the full code.
VoG is the code you have sent me

 

[VoG]

In the Visual Basic Editor, in the Project window double click ThisWorkbook. Is there any code in there (if so please post it).

 

[waxb18]

Sorry but its blank..
The engineers.xls workbook does have a macro within it do you want that?

 

[VoG]

If it is in the ThisWorkbook module, yes please.

The subscript out of range message means that Excel is looking for something that doesn't exist. The most likely reason is that there is some code looking to do something with Call Data Extract TEMPLATE.xls which the macro has just renamed.

 

[waxb18]

After close scritization of the code i made an error when i pasted it to you

The file name is ALL ENGINEERS.xls
Not engineers

IT WORKS

Thanks for the help VoG
your the ****