Hi All

in the following file Budget

https://drive.google.com/file/d/0B6D...9aRjhNMjA/view

I have to create a table based on

1. A list of sites (variable number of sites)
2. A list of costs (water, electricity, detergent, tank cleaning)
3. Months ( Jan, FebÂ…December)

the code will create/update a table where the user will input the all costs per month and site. table is located in tab “VARIABLE_COST”

1. List of sites located in tab “site assumptions”: it is starts in cell P9. The user can insert new sites and/or delete sites
2. List of costs located in tab "costs_list” starting in A2 and the user can add more costs to the current list
3. Months located in tab “VARIABLE_COST” it is already input so nothing to do


I have the following code which creates/updates the sites and costs in column A and B in tab “VARIABLE_COST” .
it works well however is not dynamic if the user change the number of sites the costs rows wont move accordingly.

if a site is deleted in tab "site assumptions" the rows related to that site in "Variable cost" must be deleted.

Whether we add new sites or delete the table in "variable Cost" must be compact (no blank rows in between sites)

[CODE]
Sub create_variable_costs_assumptions()

Dim CostLst As Range
Dim CostRws As Integer
Dim usdrws As Long
Dim i As Long
Dim ValU As Range

Application.ScreenUpdating = False
CostRws = Sheet21.Range("A2").CurrentRegion.Rows.Count
Set CostLst = Sheet21.Range("A2:A" & CostRws)


i = 2
For Each ValU In Sheet8.Range("P9:P" & Sheet8.Range("P9").End(xlDown).Row)
Sheet12.Range("A" & i).Resize(CostRws - 1) = ValU
Sheet12.Range("B" & i).Resize(CostRws - 1) = CostLst.Value
i = i + CostRws - 1
Next ValU

Application.ScreenUpdating = False




End Sub[CODE]