Microsoft Office Forums - View Single Post - [Solved] How to always keep certains lines at the top?

Logit · 04-19-2018, 06:48 AM

It sounds like you are wanting to create a TEMPLATE of your sheet so you can easily re-create it as often as needed.

Here is code to do that :

Code:

Option Explicit

Sub HyperLnksCreate()
Dim wsRecipes As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Dim i As Long
Dim wsIndex
On Error Resume Next

With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
    
    Set wsIndex = .Sheets("Index")                            'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsIndex.Range("A2:A" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
    
    Application.ScreenUpdating = False                              'speed up macro
    For Each Nm In shNAMES                                          'check one name at a time
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A2)") Then   'if sheet does not exist...
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)               '...create it from template
            'ActiveSheet.Range("A1").Value = (Nm.Text)
            ActiveSheet.Name = CStr(Nm.Text)                        '...rename it
         End If
         
    With Sheets("Index")                                                         'create hyperlinks in list
        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", _
        SubAddress:="'" & .Range("A" & i).Value '& "'!A2" ', TextToDisplay:=.Range("A" & i).Value
        Next i
    End With

    Next Nm
    
    wsIndex.Activate                                           'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end

End With

MsgBox "All sheets created"
End Sub