try this
Sub Macro456()
Dim Sht As Worksheet
Dim LastRow As Long
Dim ChartLastRow As Long
With Worksheets("Raw_data")
.Columns("L:L").Copy
End With
Set Sht = Sheets.Add
With Sht
.Name = "Input"
.Paste
End With
LastRow = Sht.Range("A65536").End(xlUp).Row
Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("A7:A" & LastRow), ActiveSheet.Range("B7"), , , False, False _
, False
Range("C1").FormulaR1C1 = "Step"
Range("D1").FormulaR1C1 = "1.24E-03"
Range("C2").FormulaR1C1 = "mean"
Range("D2").FormulaR1C1 = "=AVERAGE(R[5]C[-3]:R[1128]C[-3])"
Range("C3").FormulaR1C1 = "StDev"
Range("D3").FormulaR1C1 = "=STDEV(R[4]C[-3]:R[1127]C[-3])"
Range("F1").FormulaR1C1 = "FS"
Range("F2").FormulaR1C1 = "7.46E-01"
Range("G2").FormulaR1C1 = "6.338-01"
Range("H1").FormulaR1C1 = "MC"
Range("H2").FormulaR1C1 = "6.25E-01"
Range("I2").FormulaR1C1 = "7.57E-01"
Range("J1").FormulaR1C1 = "PCM"
Range("J2").FormulaR1C1 = "6.70E-01"
Range("K2").FormulaR1C1 = "7.90E-01"
Range("J7").FormulaR1C1 = "Frequency"
Range("F2:K2").NumberFormat = "0.000E+00"
Range("C8:C41").Copy
Range("J58").Select
ActiveSheet.Paste
Range("F4").FormulaR1C1 = "Steps for X"
Range("G4").FormulaR1C1 = "0.000622124"
Range("F7").FormulaR1C1 = "X"
Range("G7").FormulaR1C1 = "Density"
Range("H7").FormulaR1C1 = "D*N*Step"
Range("I7").FormulaR1C1 = "Value"
Range("F8").FormulaR1C1 = "0.650508224964142"
Range("F9").FormulaR1C1 = "=R[-1]C+R4C7"
Range("F9").AutoFill Destination:=Range("F9:F236"), Type:=xlFillDefault
Range("G8").FormulaR1C1 = "=NORMDIST(RC[-1],R2C4,R3C4,FALSE)"
Range("G8").AutoFill Destination:=Range("G8:G235")
Range("G8:G235").NumberFormat = "0.00E+00"
Range("H8").FormulaR1C1 = "=RC[-1]*1124*R1C4"
Range("H8").AutoFill Destination:=Range("H8:H236"), Type:=xlFillDefault
Range("H8:H236").NumberFormat = "0.00E+00"
Range("K40").FormulaR1C1 = "=0.650508224964142-R[-36]C[-4]"
Range("K39").FormulaR1C1 = "=R[1]C-R4C7"
Range("K3:K40").Cut Destination:=Range("K17:K54")
Range("K53").AutoFill Destination:=Range("K5:K53"), Type:=xlFillDefault
Range("K5:K54").Copy
Range("F8").Insert Shift:=xlDown
Range("F8:F286").NumberFormat = "0.000E+00"
Application.CutCopyMode = False
Range("F57").FormulaR1C1 = "=0.650508224964142-R4C7"
Range("K5:K54").ClearContents
Range("G8").FormulaR1C1 = "=NORMDIST(RC[-1],R2C4,R3C4,FALSE)"
Range("G8").AutoFill Destination:=Range("G8:G235")
Range("G8:G235").NumberFormat = "0.00E+00"
Range("I211").FormulaR1C1 = "180"
Range("I31").FormulaR1C1 = "180"
Range("I17").FormulaR1C1 = "180"
Range("I230").FormulaR1C1 = "180"
Range("I90").FormulaR1C1 = "180"
Range("I283").FormulaR1C1 = "180"
ChartLastRow = Sht.Range("F65536").End(xlUp).Row
Range("F7:J" & ChartLastRow).Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Input").Range("F7:J" & ChartLastRow), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.SeriesCollection(3).ChartType = xlArea
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=Input!R8C6:R286C6"
ActiveChart.SeriesCollection(2).XValues = "=Input!R8C6:R286C6"
ActiveChart.SeriesCollection(3).XValues = "=Input!R8C6:R286C6"
ActiveChart.Axes(xlValue).MaximumScale = 180
ActiveChart.Legend.LegendEntries(1).Delete
ActiveChart.Legend.LegendEntries(1).Delete
ActiveChart.Axes(xlValue).TickLabels.Font.Size = 9
ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 8
ActiveChart.SeriesCollection(2).Border.LineStyle = xlNone
ActiveChart.SeriesCollection(2).Points(24).Interior.ColorIndex = 3
ActiveChart.SeriesCollection(2).Points(204).Interior.ColorIndex = 3
ActiveChart.SeriesCollection(2).Points(10).Interior.ColorIndex = 4
ActiveChart.SeriesCollection(2).Points(223).Interior.ColorIndex = 4
ActiveChart.SeriesCollection(2).Points(83).Interior.ColorIndex = 42
ActiveChart.SeriesCollection(2).Points(276).Interior.ColorIndex = 42
End Sub
I have changed only red part
Bookmarks