Range(fromhere).Copy
Range(tohere).PasteSpecial Paste:=xlPasteValues

Public fromMM As Integer
Public fromYYYY As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
  ' The code for this event is designed to operate on a worksheet names 'Rota' containing the minibus drivers' rota for 6 months.
  '
  ' Each month's rota is held in a table headed with the name of the relevant month. Under the heading of each table are three columns.
  ' The first column holds the 'day of the week' (e.g. Mon, Tue etc), the second column is the day of the month entered by the user
  ' and the third column is the name of the driver, also entered by the user.
  '
  ' To use, the user enters a 'from date' in the heading of the sheet. This may be in any form recognised by Excel as a date
  ' (e.g. input of '1/7/10' or 'jul 10' will display as 'July 2010'). Entering a 'from date' causes the 'to date' to be shown, the data in each
  ' of the 6 month tables to be cleared and the relavant month to be shown in the heading of each month table.
  '
  ' The user then enters for each month the day of the month for which a driver is rostered and the name of the driver.
  ' Entering the day of the month causes the day of the week this day fall on for this month to be shown in the first column.
  ' The day of the month is converted to superscripted ordinal form.
  '
  ' To work, the worksheet must be named 'Rota' and the cells must be formatted as follows;
  '         The cell holding the 'from date' must be named 'FromDate' and be formatted as Custom 'mmmm yyyy'.
  '         The cell holding the 'to date' must be named 'ToDate' and be formatted as Custom 'mmmm yyyy'.
  '         The heading cells in each table containing the name of the month to which the data in it relates must be named 'Monthx'
  '           where 'x' is the month number (1 to 6). Each must be formatted as Custom 'mmmm'.
  '         The range of cells containing the day of the week in each table must be named 'dddx' where 'x' is the month number
  '           and be formatted as Custom 'ddd'.
  '         The range of cells containing the day of the month in each table must be named 'Daysx' where 'x' is the month number.
  '         The range of cells containing the driver names in each table must be named 'Driversx' where 'x' is the month number.
  '
  Dim daysRangesArray As Variant
  Dim monthRangesArray As Variant
  Dim driversRangesArray As Variant
  Dim monthIndex As Integer
  Dim yearIndex As Integer
  Dim loopCounter As Integer
  Dim inDayRangeOK As Boolean
  Dim dayOK As Boolean
  Dim dd As String
  Dim ddmmyyyy As String
  Dim ordinal As String
  Dim cellBorders As Borders
  ' Only interested in a worksheet named 'Rota'
  If ActiveSheet.Name = "Rota" Then
    daysRangesArray = Array("Days1", "Days2", "Days3", "Days4", "Days5", "Days6")
    monthRangesArray = Array("Month1", "Month2", "Month3", "Month4", "Month5", "Month6")
    driversRangesArray = Array("Drivers1", "Drivers2", "Drivers3", "Drivers4", "Drivers5", "Drivers6")
    If inRange(Target, Range("FromDate")) Then              ' User has enter a 'From Date'
      fromMM = Month(Target.Value)
      fromYYYY = Year(Target.Value)
      monthIndex = fromMM
      yearIndex = fromYYYY
      ' Loop through the month tables inserting the relevant month name in the heading
      ' Note: The month name is displayed because these cells are formatted as Custom 'mmmm' but these
      '       cells actually contain the full date in the form 1/mm/yyyy making it easy to pick up
      '       the month number and year in determining the day of the week when a day in month is entered.
      For loopCounter = LBound(monthRangesArray) To UBound(monthRangesArray)
        Range(monthRangesArray(loopCounter)).Value = DateValue("1/" & monthIndex & "/" & yearIndex)
        If monthIndex = 12 Then
          monthIndex = 1
          yearIndex = yearIndex + 1
        Else
          monthIndex = monthIndex + 1
        End If
      Next
      monthIndex = monthIndex - 1
      If monthIndex = 0 Then
        monthIndex = 12
        yearIndex = yearIndex - 1
      End If
      Application.EnableEvents = False
      
      ' Set the 'To Date'
      Range("ToDate").Value = DateValue("1/" & monthIndex & "/" & yearIndex)
    
      ' Clear the cells holding the days of the week, the days number in month and the driver's name for every month
      dddsRangesArray = Array("ddd1", "ddd2", "ddd3", "ddd4", "ddd5", "ddd6")
      For loopCounter = LBound(dddsRangesArray) To UBound(dddsRangesArray)
        Range(dddsRangesArray(loopCounter)).ClearContents
      Next
      For loopCounter = LBound(daysRangesArray) To UBound(daysRangesArray)
        Range(daysRangesArray(loopCounter)).ClearContents
      Next
      For loopCounter = LBound(driversRangesArray) To UBound(driversRangesArray)
        Range(driversRangesArray(loopCounter)).ClearContents
      Next
      
      ' Make the first cell in the day of the month column in the first month the active cell
      Range("Days1").Cells(1, 1).Select
      
      Application.EnableEvents = True
    Else
      inDayRangeOK = False
      ' See if the user has entered input in one of the days in the month cells in any of the months
      For loopCounter = LBound(daysRangesArray) To UBound(daysRangesArray)
        If inRange(Target, Range(daysRangesArray(loopCounter))) Then
          inDayRangeOK = True
          monthIndex = loopCounter
          Exit For
        End If
      Next
      If inDayRangeOK Then                          ' Yes
        dayOK = True
        ' Loop through user input. Loop needed as user may have cut/copied more than one cell for pasting
        For loopCounter = 1 To Target.Count
          dd = Target(loopCounter).Value
          ' If user input is not numeric it may be because the input is being pasted and therefore is in ordinal form.
          If Not IsNumeric(dd) Then
            If Len(dd) > 2 Then
              Select Case Right(dd, 2)
                Case "st", "nd", "rd", "th"         ' Ordinal found at end so remove it
                  dd = Left(dd, Len(dd) - 2)
              End Select
            End If
          End If
          ' If input is numeric, add the appropriate ordinal
          If IsNumeric(dd) Then
            Select Case dd
              Case 1, 21, 31
                ordinal = "st"
              Case 2, 22
                ordinal = "nd"
              Case 3, 23
                ordinal = "rd"
              Case Else
                If dd < 31 Then
                  ordinal = "th"
                Else
                  dayOK = False
                End If
            End Select
            ' Construct the full date in the form dd/mm/yyyy for the day of the month entered by the user
            ddmmyyyy = dd & "/" & Month(Range(monthRangesArray(monthIndex)).Value) & "/" & Year(Range(monthRangesArray(monthIndex)).Value)
            If Not IsDate(ddmmyyyy) Then
              dayOK = False
            End If
          Else
            dayOK = False
          End If
          
          Application.EnableEvents = False
          If dayOK Then
            ' Input is o.k. so insert it into the target cell and superscript the ordinal part
            Set cellBorders = Target(loopCounter).Borders
            Target(loopCounter).Characters.Font.Superscript = False
            Target(loopCounter).Value = dd & ordinal
            Target(loopCounter).Characters(Len(Target(loopCounter).Value) - 1, 2).Font.Superscript = True
            Target(loopCounter).Borders = cellBorders        '<---- Run-time errors occurs here
            ' Set the day of the week into the cell to the left of the target cell.
            ' Note: The heading cell for this month showing the month actually contains the date of the
            '       first of that month for that year.
            Target(loopCounter).Offset(0, -1) = DateValue(ddmmyyyy)
          Else
            ' Input is not numeric
            Target(loopCounter).Offset(0, -1) = ""    ' Clear the cell to the left of the user input cell
            ' If the user input is blank then
            '   Clear the cell to the right of the user input cell (the driver name cell)
            ' Else
            '   Tell user the input is invalid
            If Target(loopCounter).Value = "" Then
              Target(loopCounter).Offset(0, 1) = ""
            Else
              MsgBox "Day of month invalid! Value entered: " & Target(loopCounter).Value
            End If
          End If
          Application.EnableEvents = True
        Next
      End If
    End If
  End If
End Sub