Automatically add columns in summary sheet

[Thomas Kuriakose]

Dear Sirs,

We have a cost sheet entered by different users in the cost sheet details sheet. The summary sheet is linked to the Cost sheet details sheet for all values. The summary sheet will be protected by a password so no one can change the sheet for cell values.

The issue we are facing is how to replicate the number of column entries in the summary sheet, based on the number of columns in Cost sheet details sheets. The number of units are subject to change from project to project.

So, for example when the user creates 15 columns (No. of Units) for a project in Cost sheet details, the same number of columns (No. of Units) should get updated in the Summary Sheet.

Please find enclosed the file for your reference.

Kindly help in updating the summary sheet with the required number of columns

Thank you so much,

with regards,
Thomas

 

[Luke M]

I would suggest that rather than inserting/deleting columns as necessary, you setup the template to be able to accomodate the most amount of columns that will be used. Then, you have formulas on SUmmary sheet linking to headers on Input sheet. The extra columns to the right that are unused in some cases can just be blank (something like =IF(Input!M2="","",M2) would work)
or, you can hide the unneeded columns.

 

[Thomas Kuriakose]

Dear Sir,

Thank you very much for this input.

Thanks,

with regards,
thomas

 

[Thomas Kuriakose]

Dear Sirs,

I found a code on addition of columns dynamically.

But am not able to use this to get the number of columns in the summary sheet based on the cost sheet details sheet.

Sub InsertSeparatorColumns()
    Dim ws As Worksheet
    Dim firstCol As String
    Dim lastRow As Long
    Dim i As Long
    Dim howManySeparators As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    firstCol = "D"
    lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
    howManySeparators = Application.WorksheetFunction.CountIf _
                            (ws.Range("D2:D" & lastRow), "Other")

    For i = 1 To howManySeparators * 2 Step 2
        ws.Range(firstCol & 1).Offset(, i).EntireColumn.Insert
    Next i
End Sub

Kindly help to use this code in my sheet.

Thank you so much,

with regards,
thomas