Sub Add_Prebuilds()
'
'
Add_Prebuildsub:
Dim Endpoint2 As Integer
Dim StartPoint2 As Integer
Dim Location2 As String
Dim CellCheck As String
StartPoint2 = Application.InputBox("Enter the Row number where you would like to add a new section, (Important Note: New sections can only be added at the last row of an existing section . i.e. Enter the last row number of the preceeding section )", "Section Location", Type:=1)
Endpoint2 = StartPoint2 + 14
Location2 = StartPoint2 + 1 & ":" & Endpoint2
CellCheck = "A" & StartPoint2 + 1
' This confirms the section is being added in a safe location
If StartPoint2 <= 20 Then GoSub LocationError
If StartPoint2 = False Then Exit Sub
If StartPoint2 <> False Then If Range(CellCheck) = 2 Then GoSub Insertsect
GoSub LocationError
Exit Sub
Insertsect:
' This turns the protection off on the sheet
ActiveSheet.Select
ActiveSheet.Unprotect ("Password")
'Select row place to add
Rows(Location2).Select
Selection.Insert
' Copy the template blank section
Application.Goto Reference:=
Worksheets("Lighting").Range("$38:$51")
Selection.Copy
' Paste the template blank section
Rows(Location2).Select
ActiveSheet.Paste
Selection.EntireRow.Hidden = False
' This turns the protection back on for the sheet
ActiveSheet.Select
ActiveSheet.Protect Password:="Password", DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
True, AllowFiltering:=True, UserInterfaceOnly:=True
Application.ScreenUpdating = True
Exit Sub
LocationError:
Debug.Print Num
MsgBox "ERROR : The Row number selected was not the last row in an existing section. Please select a valid row."
' This turns the protection back on for the sheet
ActiveSheet.Select
ActiveSheet.Protect Password:="Password", DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
True, AllowFiltering:=True, UserInterfaceOnly:=True
Application.ScreenUpdating = True
End Sub
Bookmarks