Take certain Data from all tabs & put onto new tabs

[cSciFiChick]

I have been working on this for weeks and I just can't get something that works. If someone could please help me.

So I have some work budgets that I need to upload into another system. But the upload needs to be in a different format. Basically the data I have is in columns and I need it in rows etc

So here is an example of my data:

So I need to take this data from each tab in my worksheet (These tabs get added and removed and all have names that can change). I need the data to go into two summary tabs this can be in the same worksheet or a new worksheet.

One tab will be "Headers" which should have this data:

Job No A2 = Job number that is on a main tab in the worksheet
Change Order No B2 = C3
Change Order seq C2 = '0'
Date D2 = today()
Owner CO No E2 = C5
Status F2 = C2
Change to Contract G2 = K16
Change to Cost H2 = K17
Units I2 = G16
Comments J2 = =CONCATENATE(B3, " ",C3, " ",C4)

Then I need a tab that is "Details" which has this data:

Job No A:A = Job number that is on a main tab in the worksheet
Change Order No B:B = C3
Change Order seq C:C = '0'
Cost Code D:D = B8:B18 (once time for cost class 2 and once for cost class 1)
Cost Class E:E - There are two Cost classes one for materials (2) which would be from F8:F18 and (1) which is labor H8:H18
Cost F:F = for Cost Class 2 F8:F18 and Cost Class 1 H8:H18
Units G:G =for Cost Class 2 always = '0' for Cost Class 1 = G8:G18

 

[cSciFiChick]

I guess no one else can really solve this either that is probably why I have been working on it and can't figure out how to get it to do what I want