Generate List of Matching Rows from multiple tabs

I pull physical stats of network devices daily. Each time I pull data, it goes into a new Tab. All tabs are formatted the same, though there may be differing numbers of rows, and corresponding device data may be on a different row in each tab.

Data is formatted by row, with an authentic MAC address in the A column, and different stats through the columns. A device will only appear once per tab, and may not be on all tabs

Example of data format on each tab:
MAC Address____XXXX__Pwr1_Pwr2__XXXX__Pwr3._Pwr 4
1234.5678.0001....xxxx....52......26......xxxx......3.......34
1234.5678.0002....xxxx....47......27......xxxx.....-10.....35


My end goal is to generate a trend graph for a single device by simply entering its MAC address into a specific cell and maybe clicking a macro button. This would generate a table by searching each tab for a match of that MAC in the A column.

A match would result in 5 cells from the matched row to be entered into the report table. On and on through each tab until I have a trend table.


Something like the format below:

Date/Time___Pwr 1_Pwr 2_ Pwr 3_Pwr 4
02-10 0900___54____25_____0_____35
02-10 1600___48____25_____-1____36
02-11 0930___35____26_____-9____35
02-13 1000___52____27_____7_____34
02-13 1200___42____25_____10____37
02-13 1900___49____25_____12____38


Since the number of tabs changes daily, I dont want to be required to update a formula or macro each time I add a tab.

I've worked a little with loops and lookups, but this is just a bit too complex for me to take all the smaller ideas and link them together. If I can get the table generated, I can handle the chart.

Thanks for Any Help

First of all, Thank you. Its a great start on this little task. The code works just like I want it to. I just need to change the cells it outputs (Which I didnt provide...).

The sample chart I provided didnt have things in the right columns... it was more a visual sample. The 'XXXX' were just to show that the return data was not in consecutive columns.
D,E,F,G,J are the cells in the matched row that I need returned.

And yes, there is a header row.


Thanks again, my boss already loves the data I pulled and organized. He'll flip when I show him a graph of it

OK, I got that in there. I removed the '.value' since I didnt need that there to generate a graph.

What changes should I make so that I can have A1 be the cell where I enter the MAC? I tried replacing 'InputBox("Please enter the MAC address below.")' with just 'A1' but that didnt seem to cut it.

Is it possible to have the report data start a few rows further down? Im planning to have the line graph start in Row B, and just hiding the cells where the report data goes. That way I'll just have an 'entry box', a 'generate graph' button, and the graph below.

Thanks again for your help

The Mac address will be entered in the "Report" tab. I'm thinking "A1" is where I enter the Mac, and "B1" is where I'll put the Macro Button.

1 Row for the entry cell and button. 1 row for the graph. 2 rows in case i decide to add anything else. So the data from the report can start in row "E".

I'm a little confused when you say "Which row in column "B" do want to start at?". Right now the data generates staring at "A2". I suppose it doesn't matter to me which column it starts in, I can point the graph to wherever it is.

E2 is good

That did it for me. Though my lack of sleep apparently caused me to mix up my Rows and Columns. I meant B5 rather than E2, but that was simple enough to correct. I imagine that confused things a bit. I need to stop looking at spreadsheets

I can't thank you enough for your help. This is one I certainly wouldn't have come up with on my own.

Im not noticing any issues when I enter an incorrect mac. It just doesn't do anything.

One question though. I notice when I enter another mac without clearing the report data, it adds to the end of it. Is there an easy way to have it clear the data from B5:F5 on down before it generates the data so I dont have to manually clear it?