Apply different formulas across multiple sheets

[bhandari]

Hi,


I have a macro that is creating sheets and adding fomula to "ABSTRACT" sheet.


i have 4 sheets in my workbook
( Product1 )
( Product2 )
( Product3 )
AND
( ABSTRACT )
only( Product 1 ) sheet =>some of the cells are linked to abstract sheet(which is fullfilled my requirement)
there are 2 more sheets ( Product2 ),( Product3 ) those linked cells are different to ( Product1 ) sheet


i want changes in my macro please find the code below
There is a issue with formula..my formula need to consider remaining sheet cells also..
this code is working for same cells in all sheets.there are other cells in (sheet=product2.product3) how to link those to ("ABSTRACT") CELLS

Sub CreateSheets()
    
    Dim X           As Long
    Dim wks         As Worksheet
    Dim MyFormulas  As Variant
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlAutomatic
    
    With Sheets("Abstract")
        For X = 5 To .Cells(5, Columns.Count).End(xlToLeft).Column
            If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
                If wks Is Nothing Then


                    Sheets("Product1").Visible = True
                    Sheets("Product2").Visible = True
                    Sheets("Product3").Visible = True


            Sheets("Shapes").Visible = True


                    Sheets("Product1").Copy after:=Sheets(Sheets.Count)
            Sheets("Product2").Copy after:=Sheets(Sheets.Count)
            Sheets("Product3").Copy after:=Sheets(Sheets.Count)


                    Sheets(Sheets.Count).Name = .Cells(5, X).Value


                    Set wks = ActiveSheet


                    MyFormulas = Array("='" & wks.Name & "'!$T$105", "", "", "", "='" & wks.Name & "'!$T$109", "", "", "", "='" & wks.Name & "'!$T$124", "='" & wks.Name & "'!$T$143", "", "", "", "", "", "='" & wks.Name & "'!$T$115+'" & wks.Name & "'!$T$116", "", "='" & wks.Name & "'!$T$211", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$T$151", "", "='" & wks.Name & "'!$T$157", "", "", "", "", "", "", "", "='" & wks.Name & "'!$T$167", "", "='" & wks.Name & "'!$T$169", "='" & wks.Name & "'!$T$168", "='" & wks.Name & "'!$T$160", "", "", "='" & wks.Name & "'!$T$183", "='" & wks.Name & "'!$T$193", "", "", "='" & wks.Name & "'!$T$187", "", "", "", "", "='" & wks.Name & "'!$T$192", "", "", "", "='" & wks.Name & "'!$T$158", "='" & wks.Name & "'!$T$159", "='" & wks.Name & "'!$T$200", "='" & wks.Name & "'!$T$195", "", "", "", "='" & wks.Name & "'!$T$203", "", "", "", "", "" _
                    & "='" & wks.Name & "'!$T$196", "", "", "", "='" & wks.Name & "'!$T$199", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "='" & wks.Name & "'!$T$212*25", "='", "='")
                    .Range("A10").Offset(, X - 1).Resize(119, 1).Formula = Application.Transpose(MyFormulas)
                    ThisWorkbook.Worksheets("Abstract").Range("E130:AH130").Clear
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
            
        ActiveSheet.Protect Password:="stayaway", UserInterfaceOnly:=True
            
            Dim shape As shape
            For Each shape In ActiveSheet.Shapes
              shape.Locked = True
            Next
            
            Set wks = Nothing
            
        Sheets("Product1").Visible = xlSheetVeryHidden
            Sheets("Product2").Visible = xlSheetVeryHidden
        Sheets("Product3").Visible = xlSheetVeryHidden


            Sheets("Shapes").Visible = xlSheetVeryHidden


            
        Next X
    End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[bhandari]

any suggestions?

 

[bhandari]

can i get help?