IF,OR,Vlookup to pull multiple sheet data

The IFERROR logic you are trying to use will not work, it will not generate an error just because the result cell is empty. Also, when putting minus in front of VLOOKUP you have to wrap it with parenthesis, I got many crashes because of this.

This seems to work but I didn't implement the check for empty input cell. I did implement INDIRECT so you can just drag the formula to the right and it will work.

Formula:

Please Login or Register  to view this content.

Not sure about all those zeroes in the Sheet3 results.

This is easier on the eye!! But it's still Jacc's formula.

That solved the problem, well done!
Nevertheless, there are a few details in the code that triggered me to go all Yoda here.

Option Explicit is strongly recommended as it forces you to declare variables.
Makes troubleshooting soooooooo much easier. It also makes the code run faster but in this case it probably wouldn't be noticeable.

Another minor improvement is the addition of switching calculation on and off. I think this dataset is too small for anyone to notice the timedifference but on large data sets it makes a difference.

Copy paste is a heavy, slow process which is rarely needed. I'm doing the same thing here with just =. This will make a big difference on this workbook.
By removing the copy paste, it is also possible to remove the Activate command in most places. Activate and Select are also very slow commands, they also make a big difference even on a small workbook.
Finally I removed a lot of parameters for the sort method since those are correct by default. Looks neater, that's all.
In a half donkey attempt to be correct I passed the Summary worksheet as a parameter to the sorting Sub.

Faster the code now is.

Please Login or Register  to view this content.

Originally Posted by Jacc

That solved the problem, well done!
Nevertheless, there are a few details in the code that triggered me to go all Yoda here.

Thanks Jacc (or Yoda if you prefer). I am sort of self taught with VBA, and can see the wisdom of what you say. Some really good points I hope I can learn from and remember.

I think another improvement could be added. If we picked up each sheet name as we go through the ws loop, the column headings on the Summary sheet could be created, thus saving the user the need to do this. Obvious benefit of avoiding typos etc.

How would you code this?

DAC

Great Thanx Jacc & David.

Infact in real life I am dealing with a large data set more than 8000 rows.Since here it is limit and due to privacy only a sample I am able to post.And Rest copy paste you have already explained.

And Thanx for your more improved faster code thinking for large data set without any helper column.


This solved my issue.

Good to hear my code is appreciated!

Picking up the sheets as you go along makes a lot of sense considering the large dataset. I'm guessing the sheet numbers is the way to go:

Please Login or Register  to view this content.

Also the formula should be added via VBA for the same reason.
Getting the headers in place is fairly straight forward. As for the formulas the macro recorder is a good help. Simply start recording, hit F2 in a formula cell, then enter, then stop recording. The formula is now ready to be copied from the latest recording in R1C1 format. The R1C1 format looks the same throughout the range so once the range to where the formula should be applied has been established you apply the formula to the whole range at once like this:

Please Login or Register  to view this content.

.

Another question to ask is whether the formula should be there at all? Afterall, the same calculations can be made in VBA. An 8000 line recalc every now and then may slow things down.
I would probably go for the formula solution anyway because it only takes one tiny line of code to convert from formula to static values should that be needed:

Please Login or Register  to view this content.

Using the ' in the code it is easy to switch this line on and off to find out what works best for you.