Move data & duplicate current sheet

[TBrooker]

Hello there and thank you very much for any help received in advance. I have only recently started to learn VBA and have been putting it to practice in creating userforms to help people at my work to be able to timetable more quickly.

I have realised however that in many cases the basic code that I have been using could often be improved. However in the sections below I have not been able to work this out for myself. This needs to be done as after a large number of worksheets exists excel gradually gets slower and slower when running the macros.

Please could anyone help?

Macro 1

Private Sub CommandButton1_Click()
    oldSheet = ActiveSheet.Name
    Sheets("Schedule A GE2").Select
        Range("D5").Select
        ActiveCell.FormulaR1C1 = "='Teacher Class & Room List'!R[-3]C[-3]"
        Range("D6").Select
    Sheets("Schedule B GE2").Select
        Range("D5").Select
        ActiveCell.FormulaR1C1 = "='Teacher Class & Room List'!R[-3]C[-3]"
        Range("D6").Select
    Sheets("Schedule A GE1").Select
        Range("D5").Select
        ActiveCell.FormulaR1C1 = "='Teacher Class & Room List'!R[-3]C[-3]"
        Range("D6").Select
    Sheets("Schedule B GE1").Select
        Range("D5").Select
        ActiveCell.FormulaR1C1 = "='Teacher Class & Room List'!R[-3]C[-3]"
        Range("D6").Select
    Sheets(oldSheet).Activate
End Sub

Macro 2

Private Sub CreateButton_Click()

NewSheet = NameBox.Text
SillyThing = "Name: "
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = NewSheet
    Range(" A5").Value = SillyThing & NameBox.Text

End Sub

The second of the two macros is the real issue as due to how excel apparently names worksheets the macro will stop working after some time and the only way I have worked out to get it working again is to save and re-open the workbook. It also takes some time to run as I am a complete noob with the dim function.

Many thanks,

Tom