Make workbook stay on 1st sheet while macro is running

[shina67]

Hi All,

I have recorded quite a lengthy macro that takes a few minutes to run. Whilst running I get the normal flickering from sheet to sheet.
Is there a code I can add into my macro that will make it stay on the 1st sheet and display some text saying that the macro is running. Also when completed running it will display some text saying that it is complete. Thanks for any help in advance and where would I past the code into the macro.

 

[MARK858]

You'll need to edit your code as well as turning off screenupdating. Post your code in code tags (paste your code in the thread, select it then click the # icon) so we can look at it.

 

[shina67]

You'll need to edit your code as well as turning off screenupdating. Post your code in code tags (paste your code in the thread, select it then click the # icon) so we can look at it.

Sub Macro10()
'
' Macro10 Macro
' carry over new panel count with carry over info added
'


'
    Sheets("Schedules").Select
    Columns("A:AB").Select
    Range("A2").Activate
    Selection.Copy
    Sheets("Carry Over").Select
    Columns("X:X").Select
    ActiveSheet.Paste
    Range("Y12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Schedules!RC[-23]"
    Range("Y12").Select
    Selection.AutoFill Destination:=Range("Y12:AN12"), Type:=xlFillDefault
    Range("Y12:AN12").Select
    Selection.AutoFill Destination:=Range("Y12:AN16"), Type:=xlFillDefault
    Range("Y12:AN16").Select
    Calculate
    Range("Y12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Schedules!RC[-23]+R[-4]C[-23]"
    Range("Y12").Select
    Selection.AutoFill Destination:=Range("Y12:AN12"), Type:=xlFillDefault
    Range("Y12:AN12").Select
    Range("Y12:AN12").Select
    Range("AA12").Activate
    Calculate
    Sheets("Carry Over").Select
    Calculate
    Range("AU12:AU16").Select
    ActiveCell.FormulaR1C1 = _
        "=('Machine Throughput Averages'!R66C2*38.75)-(R[5]C[-18]+R[5]C[-15]+R[5]C[-11]+R[5]C[-10]+R[5]C[-14])"
    Range("AU17").Select
    Range("AC12:AC16").Select
    Range("AC12:AC16").Select
    Range("AC13").Activate
    Range("AC12:AC16").Select
    Range("AC14").Activate
    Range("AC12:AC16").Select
    Range("AC15").Activate
    Range("AC12:AC16").Select
    Range("AC16").Activate
    Range("AC12:AC16").Select
    Range("X19:AN19").Select
    Range("Y17").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
    Range("Y18").Select
    Range("Y17").Select
    Selection.AutoFill Destination:=Range("Y17:AN17"), Type:=xlFillDefault
    Range("Y17:AN17").Select
    Calculate
    Range("AP18").Select
    Range("AO17").Select
    Range("AO17").Select
    ActiveCell.FormulaR1C1 = _
        "=(RC[-12]+RC[-9]+RC[-5]+RC[-4]+RC[-8])/'Machine Throughput Averages'!R66C2"
    Range("AO18").Select
    Range("AP17").Select
    ActiveCell.FormulaR1C1 = _
        "=(RC[-11]+RC[-4]+RC[-3])/'Machine Throughput Averages'!R66C6"
    Range("AP18").Select
    Range("AQ17").Select
    ActiveCell.FormulaR1C1 = _
        "=(RC[-16]+RC[-18]+RC[-15])/('Machine Throughput Averages'!R66C4+'Machine Throughput Averages'!R66C5)"
    Range("AQ18").Select
    Range("AR17").Select
    ActiveCell.FormulaR1C1 = _
        "=(RC[-10]+RC[-9]+RC[-4]+RC[-18])/'Machine Throughput Averages'!R66C3"
    Range("AR18").Select
    Range("AS17").Select
    ActiveCell.FormulaR1C1 = _
        "=(RC[-20]+RC[-19]+RC[-18]+RC[-17]+RC[-15]+RC[-14]+RC[-11]+RC[-10]+RC[-7]+RC[-6]+RC[-5])/('Machine Throughput Averages'!R66C7+'Machine Throughput Averages'!R66C8)"
    Range("AS18").Select
    Range("AU12:AU16").Select
    ActiveCell.FormulaR1C1 = _
        "=('Machine Throughput Averages'!R66C2*38.75)-(R[5]C[-18]+R[5]C[-15]+R[5]C[-11]+R[5]C[-10]+R[5]C[-14])"
    Range("AU17").Select
    Sheets("Carry Over").Select
    Range("Y12:AN12").Select
    Selection.Copy
    Range("Y21").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Range("Y17:AN17").Select
    Selection.Copy
    Range("Y26").Select
    ActiveSheet.Paste
    Range("Y21:AN21").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=15
    Range("Y30").Select
    ActiveSheet.Paste
    Range("Y39").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    Range("Y48").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    Range("Y57").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-30
    Range("Y26:AN26").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Range("Y35").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    Range("Y44").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    Range("Y53").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    Range("Y62").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    ActiveWindow.SmallScroll Down:=-54
End Sub
Sub test()
'
' test Macro
' test
'


'
    Application.Run "'Scheduling Capacities.xlsm'!Full_Report"
    Sheets("Schedules").Select
    Application.Run "'Scheduling Capacities.xlsm'!Macro9"
    Application.Run "'Scheduling Capacities.xlsm'!Macro10"
    ActiveWindow.SmallScroll Down:=-27
End Sub

 

[MARK858]

Try the below (if I haven't missed something). I don't know why you have all those calculates but I have left them in although they are going to slow it all down.

Sub Macro10()
    '
    ' Macro10 Macro
    ' carry over new panel count with carry over info added
    '


    'R1C1
    Application.ScreenUpdating = False
    Sheets("Schedules").Columns("A:AB").Copy Sheets("Carry Over").Range("X1")
    Sheets("Carry Over").Range("Y12:AN12").FormulaR1C1 = Range("Y12:AN12").FormulaR1C1 = "=Schedules!RC[-23]+R[-4]C[-23]"
    Calculate
    
    With Sheets("Carry Over")
        .Range("AU12:AU16").FormulaR1C1 = _
        "=('Machine Throughput Averages'!R66C2*38.75)-(R[5]C[-18]+R[5]C[-15]+R[5]C[-11]+R[5]C[-10]+R[5]C[-14])"

        .Range("Y17:AN17").FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
        Calculate
        .Range("AO17").FormulaR1C1 = "=(RC[-12]+RC[-9]+RC[-5]+RC[-4]+RC[-8])/'Machine Throughput Averages'!R66C2"
        .Range("AP17").FormulaR1C1 = "=(RC[-11]+RC[-4]+RC[-3])/'Machine Throughput Averages'!R66C6"
        .Range("AQ17").FormulaR1C1 = _
        "=(RC[-16]+RC[-18]+RC[-15])/('Machine Throughput Averages'!R66C4+'Machine Throughput Averages'!R66C5)"
        .Range("AR17").FormulaR1C1 = "=(RC[-10]+RC[-9]+RC[-4]+RC[-18])/'Machine Throughput Averages'!R66C3"
        .Range("AS17").FormulaR1C1 = _
        "=(RC[-20]+RC[-19]+RC[-18]+RC[-17]+RC[-15]+RC[-14]+RC[-11]+RC[-10]+RC[-7]+RC[-6]+RC[-5])/('Machine Throughput Averages'!R66C7+'Machine Throughput Averages'!R66C8)"
        .Range("AU12:AU16").FormulaR1C1 = _
        "=('Machine Throughput Averages'!R66C2*38.75)-(R[5]C[-18]+R[5]C[-15]+R[5]C[-11]+R[5]C[-10]+R[5]C[-14])"
        .Range("Y12:AN12").Copy .Range("Y21")
        Calculate
        .Range("Y17:AN17").Copy .Range("Y26")
        .Range("Y21:AN21").Copy .Range("Y30")
        .Range("Y21:AN21").Copy .Range("Y39")
        .Range("Y21:AN21").Copy .Range("Y48")
        .Range("Y21:AN21").Copy .Range("Y57")
        .Range("Y26:AN26").Copy .Range("Y35")
        .Range("Y26:AN26").Copy .Range("Y44")
        .Range("Y26:AN26").Copy .Range("Y53")
        .Range("Y26:AN26").Copy .Range("Y62")
    End With
    Calculate
    Application.ScreenUpdating = True
End Sub