Insert columns to multiple sheets based on list

[ElPolloDiabl]

Hi all, I'd need help with this task I've been assigned. Sample file attached.

So basically I'd need to keep a list of all the projects in the "Projects" sheet. And when a project name is added to the list, 2 new columns (see sample) would be inserted to the 3 other sheets (Summary, 2014, 2015) and in the right place, based on the project number (from lowest to highest). The main thing is to keep the structure of these 3 sheets exactly the same at all times, when new projects are added.

I'm helplessly lost when it comes to VBA so I'd be truly grateful if someone could take the time to help out. Huge thanks in advance!

[Yongle]

Attached is your amended workbook. I have provided code that does what you have asked.
Go to sheet named "Projects" and click on the button to add a new project. It defaults to the next sequential number, but that can be overwritten if you want.
I have added a sheet named "Dummy" to hold the area that needs copying to create the 2 additional columns each time.
If you delete a project after adding it then you should delete the columns and not the values (UsedRange.Columns.Count will treat the columns as occupied because if formatting left behind)

Sub AddProject()

With Sheets("Projects")
PrevProj = Left(.Range("A3").End(xlDown).Value, 4)
NextProj = PrevProj + 1 & " - "


NewProject = InputBox("Input New Project", "New Project", NextProj)
.Range("A3").End(xlDown).Offset(1, 0).Value = NewProject
End With

With Sheets("2015")
nextcol = .UsedRange.Columns.Count + 1
Sheets("Dummy").Range("B1:C23").Copy
.Cells(1, nextcol).PasteSpecial xlAll
.Cells(2, nextcol).Value = NewProject
End With


With Sheets("2014")
nextcol = .UsedRange.Columns.Count + 1
Sheets("Dummy").Range("B1:C23").Copy
.Cells(1, nextcol).PasteSpecial xlAll
.Cells(2, nextcol).Value = NewProject
End With


With Sheets("SUMMARY")
nextcol = .UsedRange.Columns.Count + 1
Sheets("Dummy").Range("B1:C23").Copy
.Cells(1, nextcol).PasteSpecial xlAll
.Cells(2, nextcol).Value = NewProject
End With

[Yongle]

Project.jpg

Some thoughts:

1 There is no link between 2014/2015 sheet values and the Summary sheet
- duplicate input, copy and paste?, input via vba?
2 Each worksheet has alternative columns for values and percentages (see image above)
- dynamically summing every other column is tricky. One way to avoid this would be allocate a special area for the raw data (in adjacent columns) plus the totals, with a matching area for the percentages, and then pull them together at project and summary level.