Hello,

I have written the below code fragment in Excel Vba. It copies information
from several data sheets to an output sheet if the data sheetname is
contained within a "map" and has been flagged with a "Y".
This works fine if all the sheetnames are contained within the "map". I
noticed however that vlookup falls over if I add new data sheets and don't
remember to update the map at the same time.
Could someone advise as to how to trap the error?

For Each wks In ActiveWorkbook.Sheets
If Application.VLookup(wks.Name, Range("Map!b2:c200"), 2,
False) = "Y" Then
Sheets("Output").Range("A:A").Offset(0, x + 1) =
wks.Range("J:J").Value
x = x + 1
End If
Next wks

On Nov 14, 7:13 am, "Graeme" <graeme wrote:
Hello,

I have written the below code fragment in Excel Vba. It copies information
from several data sheets to an output sheet if the data sheetname is
contained within a "map" and has been flagged with a "Y".
This works fine if all the sheetnames are contained within the "map". I
noticed however that vlookup falls over if I add new data sheets and don't
remember to update the map at the same time.
Could someone advise as to how to trap the error?

For Each wks In ActiveWorkbook.Sheets
If Application.VLookup(wks.Name, Range("Map!b2:c200"), 2,
False) = "Y" Then
Sheets("Output").Range("A:A").Offset(0, x + 1) =
wks.Range("J:J").Value
x = x + 1
End If
Next wks

Try this:

Dim wks As Excel.Worksheet
Dim x As Long
Dim vValue As Variant

For Each wks In ActiveWorkbook.Sheets
vValue = Application.VLookup(wks.Name, Range("Sheet1!e2:f9"), 2,
False)
If Not IsError(vValue) Then
If vValue = "Y" Then
Sheets("Output").Range("A:A").Offset(0, x + 1) = wks.Range
("J:J").Value
x = x + 1
End If
End If
Next wks
Set wks = Nothing

I'm not sure this line is going to work = Sheets("Output").Range
("A:A").Offset(0, x + 1) = wks.Range("J:J").Value