Code:
If Sheets(y).Name <> "Lookup" or _
sheets (y).Name <> "Test" Then
Think about the logic of that statement for a second.

If the sheet name is Test then it is <> Lookup so the If is true
If sheet name is Lookup then it is <> Test so again it will be true
If sheet name is anything else then it will be <> Test And <> Lookup so it will again be true

I can't tell what you are trying to exclude here btu your code will nto exclude anything. If those 2 sheets are the ones you want to exclude then the proper logic would be.

Code:
If Not Sheets(y).Name = "Lookup" And _
Not sheets (y).Name = "Test" Then