How to get a macro to work in all worksheets

I know I am missing something simple here. I made a macro to create a chart from a template based on two ranges in a worksheet (Brisbane April Sales Input). I then copy the page and rename the new page (Brisbane May Sales Input), and copy my macro button across to the new page. I have spent hours today trying to change the code so that the macro will work on all new sheets but I keep getting error codes. I have been trying to change the coding to active worksheet. Could someone please show me the changes that I should make. I reference two cell row ranges for the chart and a cell containing text for the chart title. Current coding is as follows:

Sub MelbourneMonthlyProfitChart()
'
' MelbourneMonthlyProfitChart Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Range("A1:G1,A5:G5").Select
Range("A5").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ApplyChartTemplate ( _
"C:\Users\Robertson\AppData\Roaming\Microsoft\Templates\Charts\Travel Destination Monthly Chart for Managers resized.crtx" _
)
ActiveChart.SetSourceData Source:=Range( _
"'Melbourne April Sales Input'!$A$1:$G$1,'Melbourne April Sales Input'!$A$5:$G$5" _
)
ActiveChart.ChartTitle.Select
Selection.Caption = "='Melbourne April Sales Input'!R5C1"
End Sub

any help will be very much appreciated