How to link cell reference to 1 sheet to another sheet ?

[santoshloka]

i need help with macro


i have made a code to create new sheet with help of cells reference ("E2:AG2") there are around 20 sheets which are going to generate by macro(when the sheets are created they must link to Abstract sheet)
There is a issue with making sheets
when the new sheet created it must link with particulat cell to get the cell value in Abstract sheet.

if you look at "Abstract" sheet(cell formula it is linked to "11M") sheet, as well evrey "new sheet("E2:AG2") must be linked to

='11M'!$I$65=E6
=22+770M'!$I$65=G6


when i click command command new sheets are genereated by code as well as formula also must link to abstract sheet.

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
     
    Set MyRange = Sheets("Abstract").Range("E2:AG2")




    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

Hers is the attachment
https://drive.google.com/open?id=0B8-usD7SI_4aQVRUaV91WEI2cHc

 

[santoshloka]

can i get help here..iam struck at here,someone please bring me out of this

 

[Misca]

If you want to enter the formulas before the sheets exist you might wanna use IFERRORs and/or INDIRECTs in your formulas.

Other way would be to create the worksheet first and then add the formulas using the sheetname as a variable:

Sub CreateSheetsFromAList()    Dim MyCell As Range
    Dim MyRange As Range
    Dim MySheetName As String
    Dim MyFormulas As Variant


     
    Set MyRange = Sheets("Abstract").Range("E2:AG2")




    For Each MyCell In MyRange
    
        MySheetName = MyCell.Value
        MyFormulas = Array("='" & MySheetName & "'!$I$65", "", "='" & MySheetName & "'!$I$80", "='" & MySheetName & "'!$I$88", "='" & MySheetName & "'!$I$94", "='" & MySheetName & "'!$I$99", "", "='" & MySheetName & "'!$I$111", "='" & MySheetName & "'!$I$117", "='" & MySheetName & "'!$I$121", "", "='" & MySheetName & "'!$I$127", "='" & MySheetName & "'!$I$132", "='" & MySheetName & "'!$I$134", "='" & MySheetName & "'!$I$135", "='" & MySheetName & "'!$I$138", "='" & MySheetName & "'!$I$141", "='" & MySheetName & "'!$I$144", "", "='" & MySheetName & "'!$I$149", "='" & MySheetName & "'!$I$150", "='" & MySheetName & "'!$I$151", "='" & MySheetName & "'!$I$152", "='" & MySheetName & "'!$I$155", "='" & MySheetName & "'!$I$158", "='" & MySheetName & "'!$I$164", "='" & MySheetName & "'!$I$167", "='" & MySheetName & "'!$I$170", "='" & MySheetName & "'!$I$173", "='" & MySheetName & "'!$I$124", "='" & MySheetName & "'!$I$125", "=1")
        
        If SheetExists(MySheetName) Then
        
        'Sheet already exists: No need to create a new one
        
        Else
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MySheetName ' renames the new worksheet
        End If
        
        MyCell.Offset(3, 0).Resize(32, 1).Formula = Application.Transpose(MyFormulas)   'Enter formulas
        
    Next MyCell
End Sub


 Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet


     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function

Not all your values on your MyRange are valid sheet names though: You can not use wildcards (* or ?) in them ("3x5.8M" works but "3*5.8M" does not).

 

[santoshloka]

liile more help i needed
in "11 M " sheet..i have to copy this sheet including pictures and paste it in new sheets ("E2:AG2") Those are generated with referece of "Abstract sheet"

i want to go with your code because i dont know hw to use indirect formula for not existed sheets
if possible iam intrested to know that formula how it will be looks like

Your code is absolutely working fine..

whatever value is coming in "Abstract Sheet" it is not coming in center position..can we fix this?

 

[Misca]

Here's an example:

=IFERROR(INDIRECT("'"&E$2&"'!$I$65"),0)

The formula returns zero if there's no such sheet but returns the cell value when the sheet exists.


Unfortunately I don't understand the "it is not coming in center position"-part. If you mean the sheet name doesn't have to be exactly the same, replace the current line

MySheetName = MyCell.Value

with something like

MySheetName = Replace(Replace(MyCell.Value, "*", "x"), "?", "S")

If that's not what you meant, I don't think I can help you.

 

[santoshloka]

if sheet exist pop up the message box "sheet tab is already existed" how to write it?

 

[Misca]

Add this line to the code:

        'Sheet already exists: No need to create a new one[B]        
MsgBox "Sheet " & MySheetName & " already exists!", vbOKOnly, "Oops!"[/B]

 

[santoshloka]

Hey, I have Data In Template sheet as wll as pictures which i want to copy in all sheets with same location..can u add this code in your code