find value in multiple sheets, copy offset and paste to summary sheet

**Pholic** · 10-04-2011, 05:14 PM

ok. there is a list of sample numbers in column G, and results in column I. Every so often we run check values to make sure our instruments are still calibrated. every check value is labeled "LFB" in column G, with a corresponding result in column I. there is a new worksheet in the same workbook for every new day we run our testing.

I would like to create a macro that will find every LFB we've run on all worksheets in the workbook, copy the corresponding result from column I, and paste it into a column on separate sheet in the workbook named LFBsummary (in my code i have column h to paste results but any will work). I only need this done once, and not a continually updating thing or anything more advanced. Ive read through the forums and used a few codes but they don't seem to return values. It seems simpler to me than the other macro i used for copying fixed values throughout these sheets... but i'm having no luck, any advice/help would be greatly appreciated.

**tigeravatar** · 10-04-2011, 05:24 PM

Pholic,

Welcome to the forum! Give the following a try:

Please Login or Register  to view this content.

**Pholic** · 10-07-2011, 01:48 PM

Thank you very much about the quick response... I tried out the code you gave me but i'm getting an error on the first autofilter line ".Autofilter 1, "LFB""... it's acting as if LFB is a variable that has not been set yet... which seemed to be where my code was failing. I've done some vbs before but not too much with excel and it's errors/operations etc. Thanks again for all the info. - nicholas

**tigeravatar** · 10-07-2011, 02:25 PM

Pholic,

Can you post a sample workbook?

**Pholic** · 10-07-2011, 04:50 PM

unfortunately my boss won't let my post a scrubbed example of our data, so i can't help you there... on the other hand i can post the working code that and that may help point you in some direction... whether or not we're moving forward or side to side is your call, haha.

Please Login or Register  to view this content.

now this is a bit simpler considering it just copy/paste info in static cells to a summary page vs matching a value and copying an offset, but at this point even capturing the whole row with "LFB" in it would help me create a summary of the data i need much faster than manually going through 1000 worksheets in 20 workbooks... thanks again for all the help, i greatly appreciate it... nothing more frustrating than being so close to the correct code and not knowing enough scripting to polish it off. - nicholas

**tigeravatar** · 10-07-2011, 05:22 PM

Nicholas,

The code you posted takes data from several cells on different rows. It also doesn't look for LFB in column G at all, let alone return corresponding values, so I'm not sure if this contradicts your original request. Additionally, what's the error you're getting? The "LFB" is a string value, not a variable, so its definitely not looking for a variable value. If items in column G have text that contains "LFB" instead of equaling "LFB" then you would want to change that to "*LFB*"

Without a sample workbook however, there's not much more I can do. Attached is an example workbook so you can see how the macro works when it runs successfully. Perhaps I've got the wrong idea of what you need.

**Pholic** · 11-02-2011, 12:23 PM

Yes thank you very much. I'm sorry I haven't had much time to review/mess around with the project lately, it got put on the backburner so to speak. your example works perfectly, altho oddly enough i couldn't copy/use your code, but copying my sheets and putting in your workbook works just as well for me. thank you again for all the help and sorry if i made it confusing at all.