Running out of resources

I have an excel workbook that I have a number of ActiveX command buttons to run macros. After using the macros a number of times I get "Excel cannot complete this task with available resources. Choose less data or close other applications" when trying to open another workbook. What do I need to put into my macros to release resources when the macro ends.

Here goes, this is my first time so bear with me if I don't quite get it right

I will include one of the macros as it is typical of the macros in the workbook. The macros are either adding or deleting rows, formatting them and adjusting formulas that run 'through' or 'over' them

firstly initial definitions

Dim rcounter As Integer
Dim ccounter As Integer
Dim vcontractor As String
Dim vcolour As Integer
Dim vcolouri As Integer
Dim vcolourf As Integer
Dim lastrow As Integer
Dim lastcol As Integer
Dim curr_row As Integer

Sub Insert_activity_row()
'Insert Activity Row
'there are three location options defined by the current row
'       on a header row
'       on a planned row or if an actual row move to planned row directly above
'       on end of programme row
' all others to flag message box with instructions
'check that current line is a "planned" line
Application.ScreenUpdating = False
lastcol = Range("last_day").Column
Dim row_1 As Boolean, row_last As Boolean, row_header As Boolean, row_above_is_header As Boolean
If Cells(ActiveCell.Row, 7).Value = "A" Then ActiveCell.Offset(-1, 0).Select
curr_row = ActiveCell.Row
If Cells(curr_row - 3, 1).Value = "No" Then row_1 = True Else row_1 = False
If Cells(curr_row + 1, 1).Value = "insert extra rows before here" Or Cells(curr_row, 1).Value = "insert extra rows before here" Then row_last = True Else row_last = False
If Cells(curr_row, 7).Value = "H" Then row_header = True Else row_header = False
If Cells(curr_row - 1, 7).Value = "H" Then row_above_as_header = True Else row_above_as_header = False
If row_last Then
    ActiveCell.Offset(-2, 0).Select
    hgt = ActiveCell.Height
    ActiveCell.EntireRow.Insert shift:=xlDown
    ActiveCell.EntireRow.Insert shift:=xlDown
    Range(Cells(ActiveCell.Row + 2, 1), Cells(ActiveCell.Row + 3, lastcol + 1)).Copy Destination:=Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + 1, lastcol + 1))
    Range(Cells(ActiveCell.Row + 2, 2), Cells(ActiveCell.Row + 3, 6)).Value = ""
    Range(Cells(ActiveCell.Row + 2, 8), Cells(ActiveCell.Row + 3, lastcol + 1)).Value = ""
    Cells(ActiveCell.Row + 2, 3).Select
Else
    hgt = ActiveCell.Height
    ActiveCell.EntireRow.Insert shift:=xlDown
    ActiveCell.EntireRow.Insert shift:=xlDown
End If
If row_1 Then
    Cells(ActiveCell.Row, 1).Select
        ActiveCell.Value = 1
    With Cells(ActiveCell.Row, 1).Font
        .Name = "Arial Narrow"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
        .Bold = True
    End With
    With ActiveCell
        .RowHeight = hgt
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(1, 0).RowHeight = hgt
    If row_header Then
        s_formula = "=A" & ActiveCell.Row & "+1"
        Cells(ActiveCell.Row + 3, 1).Formula = s_formula
    Else
        s_formula = "=A" & ActiveCell.Row & "+1"
        Cells(ActiveCell.Row + 2, 1).Formula = s_formula
    End If
Else
    If row_header Then
        s_formula = "=A" & ActiveCell.Row - 2 & "+1"
        Cells(ActiveCell.Row, 1).Formula = s_formula
        s_formula = "=A" & ActiveCell.Row & "+1"
        Cells(ActiveCell.Row + 3, 1).Formula = s_formula
    Else
        If row_above_as_header Then
            s_formula = "=A" & ActiveCell.Row - 3 & "+1"
            Cells(ActiveCell.Row, 1).Formula = s_formula
        Else
            s_formula = "=A" & ActiveCell.Row - 2 & "+1"
            Cells(ActiveCell.Row, 1).Formula = s_formula
        End If
        If Not row_last Then
            s_formula = "=A" & ActiveCell.Row & "+1"
            Cells(ActiveCell.Row + 2, 1).Formula = s_formula
        End If
    End If
End If
'set variable for line thickness to bottom edge dependant on mid sheet or bottom row
Dim lineweight As XlBorderWeight
If row_last Then lineweight = xlMedium Else lineweight = xlThin
Z = 1
If Cells(ActiveCell.Row, 1).Value > 1 Then
    Do While Cells(ActiveCell.Row - Z, 7).Value <> "P"
        Z = Z + 1
    Loop
    Cells(ActiveCell.Row - Z, 4).Copy Destination:=Cells(ActiveCell.Row, 4)
    Cells(ActiveCell.Row, 4).Value = ""
Else
    Do While Cells(ActiveCell.Row + Z, 7).Value <> "P"
        Z = Z + 1
    Loop
    Cells(ActiveCell.Row + Z, 4).Copy Destination:=Cells(ActiveCell.Row, 4)
    Cells(ActiveCell.Row, 4).Value = ""
End If
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + 1, lastcol + 1)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
For Z = 8 To lastcol Step 7
    Range(Cells(ActiveCell.Row, Z), Cells(ActiveCell.Row + 1, Z + 6)).Select
    Selection.Borders(xlDiagonalDown).linestyle = xlNone
    Selection.Borders(xlDiagonalUp).linestyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .linestyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = lineweight
    End With
    With Selection.Borders(xlEdgeRight)
        .linestyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
        .linestyle = xlContinuous
        .ColorIndex = 48
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideHorizontal).linestyle = xlNone
Next Z
Range(Cells(ActiveCell.Row, lastcol + 1), Cells(ActiveCell.Row + 1, lastcol + 1)).Select
    Selection.Borders(xlDiagonalDown).linestyle = xlNone
    Selection.Borders(xlDiagonalUp).linestyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = lineweight
    End With
    With Selection.Borders(xlEdgeRight)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideHorizontal).linestyle = xlNone
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + 1, 7)).Select
    Selection.Borders(xlDiagonalDown).linestyle = xlNone
    Selection.Borders(xlDiagonalUp).linestyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = lineweight
    End With
    With Selection.Borders(xlEdgeRight)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .linestyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideHorizontal).linestyle = xlNone
Cells(ActiveCell.Row, 7).Value = "P"
Cells(ActiveCell.Row + 1, 7).Value = "A"
Range(Cells(ActiveCell.Row, 7), Cells(ActiveCell.Row + 1, 7)).Select
With Selection.Font
    .Name = "Arial Narrow"
    .FontStyle = "Italic"
    .Size = 8
End With
Range(Cells(ActiveCell.Row + 1, 1), Cells(ActiveCell.Row + 2, lastcol + 1)).Select
With Selection.Borders(xlInsideHorizontal)
    .linestyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Cells(ActiveCell.Row - 2, 3).Select
Application.ScreenUpdating = True
End Sub

Not the most elegant of coding, but I am a novice. there is certainly room for breaking it down to use branching to subroutines or function