look up across multiple sheets

alanlambden

Board Regular
Joined
Nov 20, 2014
Messages
70
Hi everyone,

I have an excel workbook with many tabs/worksheets. Each worksheet is in pretty much the same format. The thing im trying to do will work much easier by an automated VBA macro. Something that a little beyond me but I'm getting there.

What I want to do manulally is this:

In my multi-tab work book, each tab has 4-6 columns. In the A column is the index number (sampno). In columns B and C are two related data numbers. Sometimes there are columns D and E, also related. The index numbers in A are numbered in ascending order, five digits usually like 78001, 78002 ... etc but sometimes is skips a sequence of numbers. What I want is to look up in the first column (sampno) any number that ends with a "0' (a zero) and return the entire row into a new blank sheet.

Is is possible to write code that can go through all the tabs and tabulate my "0" columns in the first sheet? It would also be great if the code stopped when it got to the non-five digit numbers at the bottom of the file. You'll see in the mock sheet in the dropbox folder that the rows don't start at A1 .. often further down the sheet due to titles and some id information.

Here is a dropbox link to a mock spreadsheet and some images of what i am talking about.

https://www.dropbox.com/sh/fawv85o0onnezju/AABW11AfBgfKhJq-s-NfgBS4a?dl=0
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top