Summary sheet of multiple sheets

[Olavfinnerud]

Hi,

I am working with making a template in excel for writing hours at my workplace. In sheet one named “Oppsumering” I want to have a summary from the other sheets I add. Each sheet represents one week and contains the hours from different tasks. The sheet named “Uke—” is the layout for where you write hours for one week. The column C6:C17 is where you write the hours and the “resource” used. I need help with getting this information over to the summary sheet. The resource I write in the C6 cell should appear in the blank merged cells B9:D9 in the first sheet which is the summary sheet. I also need the information in the cells C15,C16,C17 to be inserted into the summary sheet under “Timer”, “Kost” and “Sum kost” respectively.

I have a button called “Ny ressurs”, when I press this the column C6:C17 gets copied and inserted into D6:D17 with blank cells so I can write in a new “Resource”. The columns in the D and F columns gets pushed one to the right (This two columns should not be added to the summary sheet). Everytime I add a new resource and writes the hours used for this resource, this should be inserted into the summary sheet. The same resources is used by different people, in the row 7 cell I write the named of the person who has used the resource. If I add multiple columns of the same resource, for example two people have used “Prosjektleder” I just want one “Prosjektleder” on the summary sheet. And the “Timer”, “Kost” and “sum” should be added together on the summary sheet next to the corresponding resource.

If you could, you can set it up so when I press the “Ny ressurs” button the resource also gets added to the summary sheet. I have attached pictures of the sheets.
I have tried to solve this problem by selecting the cells with the information i want to appear on the summary sheet and transpose and paste it to the summary sheet. But this dident work with merged cells and i also couldent make it do it also for the new resources i add.

All help is really appreciated

The code i have for the button mentioned to add a new resource is, (I dont know if this is relevant for the problem) :

Sub NY_2()
'Add column and push columns to rigth
Range("C6:C17").Copy
Range("C6").Insert Shift:=xlToRight
Range("D8:D14").ClearContents
Application.CutCopyMode = False
End Sub

 

[C Moore]

Power query would handle what you've listed.

 

[Olavfinnerud]

Power query would handle what you've listed.

Ye looks like it, but i cant figure out how to do it the way i need.