Here it is Peter. I need this to extract data from various woorkbooks/sheets using vlookup function.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[7],ZINV.XLS!C1:C10,5,0)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C245")
Range("C2:C245").Select
Range("C2").Select
Calculate
Range("C2").Select
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[6],ZINV.XLS!C1:C10,6,0)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D245")
Range("D2:D245").Select
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Garamond"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 55
End With
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],ZINV.XLS!R1:R65536,2,0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],ZINV.XLS!R1:R65536,10,0)"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I245")
Range("I2:I245").Select
Range("K2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],ZINV.XLS!R1:R65536,2,0)"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K245")
Range("K2:K245").Select
Range("C2:D245").Select
Selection.Copy
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("I2:I245").Select
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 1
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 1
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("L2").Select
End Sub