Please i want to refer data from multiplesheet.

[Pisitju]

I try to use vba code for refer data range"D9" from multisheet to index file (formula in range =VLOOKUP($D$8,'company name'!D4:H319,2,FALSE))
when use code formula in index sheet not same ( =VLOOKUP($D$8,'company name'!#REF!,2,FALSE))

Why code not shown =INV20112601!D9

please help me

thank you.



Sub tgr()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim wsDest As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wsDest = Sheets("Index")

For Each ws In ActiveWorkbook.Sheets
If ws.Name <> wsDest.Name Then
ActiveSheet.Range("D9").Copy
wsDest.Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial
End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[NdNoviceHlp]

Hi Pisitju and Welcome to the Board! Not real sure if I understand your question but this seems wrong...

ws.Range("D9").Copy

Your enable events also has to be returned to true before leaving the sub. HTH. Dave
ps. Please use code tags