Copy values from different cell of multiple worksheets into main sheet

Hi,

I have 15000 worksheets. I have already coding for opening all the worksheet and closing it.

My issue is: I have to copy 108 values from multiple worksheets into one main MASTER sheet. But every sheet has different layout. How can I use one coding for all the sheets to copy specific data into the respective column of Master sheet.

For eg:
Master sheet header is: Meter No, Device ID, Program File ID, Hardware ,Firmware

In sheet 1: Meter No: B36,Device ID:C27 , Program File ID: E28 , Hardware: A42 ,Firmware: B42
In sheet 2: Meter No: B37,Device ID:C26 , Program File ID: E27 , Hardware: A43 ,Firmware: B43
In sheet 3: Meter No: B38,Device ID:C25, Program File ID: E26 , Hardware: A44 ,Firmware: B44

and so on for 15000 sheets.

Please help me in figuring this out.

your help is appreciated!

Sorry if I'm not helping here.

Just wondering, how do you decide :

In sheet 1: Meter No: B36,Device ID:C27 , Program File ID: E28 , Hardware: A42 ,Firmware: B42

That the 106 cell values to be copied in each 15000 sheets are cell bla under Meter header, cell blo under Device header, cell blu under Program header, and so on ?

If it's "on the fly", then I think the code cannot be done.
"on the fly" ---> you look at it, then you decide "oh... this is the cell that must be copied" for 106 times.
No rule, No condition : If.... Then this cell is to be copied. So it's up to the user which his decision depends on his mood

If there is one rule : If.... Then this cell is to be copied,
and this same rule apply to all 15000000 sheets, then I think the code still can be done...
though maybe a complicated one and takes time.

Hi karmapala ,

It should search by the header namer. Like for column A it should search for Meter Number in 2nd sheet and paste the value next to Meter Number in Master sheet under Column A,and the same goes for rest of the columns.

Basically my header will be the search string in other sheets for the value to look up.

Hi Maryam...

What still I can't figure out, how to know that (for example) :
1. Under METER header, row .... is to be copied
2. Under DEVICE header, row .... is to be copied
3. Under PROG header, row .... is to be copied
.
.
.
105. Under BLA header , row .... is to be copied
106. Under BLU header , row .... is to be copied

Because I thought that in each sheet of your 15000 sheets,
each has the same 106 header name across the column
where under those 106 headers you want to copy one specific cell,
so there are 106 values to be copied.

Example: (as you said different layout in each sheet - but I assumed that all the 106 headers name will be there).
In sheet-1-of-15000, header METER is in cell A1, header DEVICE is in cell C1, .... ----> header 106th is in cell AA1
In sheet-2-of-15000, header METER is in cell G1, header DEVICE is in cell P1, .... ----> header 106th is in cell ZZ1
In sheet-3-of-15000, header METER is in cell AAA1, header DEVICE is in cell Q1, .... ----> header 106th is in cell AC1
In sheet-4-of-15000, header METER is in cell AK1, header DEVICE is in cell MM1, .... ----> header 106th is in cell S1
.
.
.
In sheet-15000-of-15000, header METER is in cell D1, header DEVICE is in cell KL1, .... ----> header 106th is in cell X1

it should search for Meter Number

Yes I understand that.... search for the header named METER, so this is the column.
But the thing which I still don't get it is how to determine for the row : (for example)
In sheet-1-of-15000 :
it is row 100 under header METER (so, A100) is to be copied
it is row 25 under header DEVICE (so, C25) is to be copied
.
.
.
it is row 349 under the 106th header (so, AA349) is to be copied

In sheet-4-of-15000 :
it is row 5 under header METER (so, cell AK5) is to be copied
it is row 27 under header DEVICE (so, cell MM27) is to be copied
.
.
.
it is row 123 under the 106th header (so, cell S123) is to be copied


So... while the header name can be search with :
IF in sheet-1-of-15000 is found a text "METER" ---> result : found in cell A1 (column A)
THEN how to determine that it is row 100 (in column A) to be copied ?


Please correct me if I'm wrong