VBA - add formula to cell when created

[Golaidron]

Hi guy's/guru's

I got have
- 1 Template sheet
- 1 Datasheet
- Named ranges x 12:
- product_January to product_December
- Tables x 12
- tblJanuary to tblDecember
- 1 Helpsheet
- Named ranges:
- Companies
- Contactperson
- Type_Change
- Tables
- Customers
- 1 Productsheet
- Named ranges:
- Ctrl_Product
Range = A3:A57
- Ctrl_Type
Range = L2:Q2
- 1 Macro

This macro

    Dim monthNames As Variant
    Dim templateSheet As Worksheet
    Dim newSheet As Worksheet
    Dim i As Integer
    Dim j AS Integer
   
    ' Legg til månedsnavn i array
    monthNames = Array("January", "February", "March", "April", "May", "June", _
                    "July", "August", "September", "October", "November", "December")
   
    ' Referer til templateSheetet
    Set templateSheet = Sheets("Template")
   
    ' Loop gjennom each month and create sheet
    For i = LBound(monthNames) To UBound(monthNames)
        'Copies Template to a new sheet
        templateSheet.Copy After:=Sheets(Sheets.Count)
        ' Set the reference to the new sheet
        Set newSheet = ActiveSheet
        ' Rename the new sheet with the month name
        newSheet.Name = monthNames(i)
       
        ' Set Datavalidation in B2:B1000 to named range product_monthNames
        With newSheet.Range("B2:B1000").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=product_" & monthNames(i)
        End With
       
        ' Set Datavalidation i C2:C1000 list to named range type_change
        With newSheet.Range("C2:C1000").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=type_Change"
        End With
       
        ' Set Datavalidation i E2:E1000 list to named range Companies
        With newSheet.Range("E2:E1000").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=Companies"
        End With
Next I
End Sub

I also want to add a formula in D2:D1000, F2:F1000 and G2:G1000, and here is where i really need you guys.

D2:D1000 - Formula
In sheet January!D2
=IFError(Index(tblJanuary[[Import]:[Provision]],Match(January!B2,tblJanuary[Product - January],0),Match(January!C2,tblJanuary[[#Headline],[Import]:[Provision]],0)),"")
In January!D3
=IFError(Index(tblJanuary[[Import]:[Provision]],Match(January!B3,tblJanuary[Product - January],0),Match(January!C3,tblJanuary[[#Headline],[Import]:[Provision]],0)),"")
and so on
I would this forumal to be added to D2:D1000 in the new sheet when created.
Is it possible to do something like this?
"=IFError(Index(tbl"&monthName&"[[Import]:[Provision]],Match("&monthName&"!B"&j&",tbl"&monthName&"[Product - &"monthName"&],0),Match("&monthName&"!C"&j&",tbl"&monthName&"[[#Headline],[Import]:[Provision]],0)),"")"

F2:F1000
In January!F2
=IFError(Index(tblCustomer[Contactperson],Match(Januar!E2,tblCustomer[Company],0)),"")
I would this forumal to be added to F2:F1000 in the new sheet when created.
Is it possible to do something like this?
"=IFError(Index(tblCustomer[Contactperson],Match("&monthName&"!E"&j&",tblCustomer[Company],0)),"")"

G2:G1000
In January!G2
=IFError(Index(Product!$L$3:$Q$57,Match(January!B2,Ctrl_Product,0),Match(January!C2,Ctrl_Type,0)),"")
I would this forumal to be added to F2:F1000 in the new sheet when created.
Is it possible to do something like this?
"=IFError(Index(Product!$L$3:$Q$57,Match(&monthName&"!B"&j&",Ctrl_Product,0),Match("&monthName&"!C"&j&",Ctrl_Type,0)),"")"

Hope to hear from you soon.

Best regards
Golaidron

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Golaidron]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Thank you Peter.