look up across multiple sheets


Board Regular
Nov 20, 2014
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.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...