Relative address rather than absolute address in VBA code (macro)

[ceepee]

Hi

I have developed a monthly report that updates automatically each month once I paste the new month's raw data to the correct sheet.

Firstly I save the report as the new month eg "July report" to "August report". Problem is when I activate the macro to automatically open, copy and paste the raw data it will always go to the "July report" (old report) instead of going to the "August report" or whichever is the new month report.

Is there a way that I can replace the code to pick up from the title of the report (new month name eg August) rather than always going to the "July report".

See attached small excerpt from the macro code used that needs to be changed.

thanks

Sub mcrrefreshallcc()
'mcrrefreshallcc Macro
' this macro copies and pastes the GL Raw Data, AP Raw Data and CR Raw data from the branch report then opens all the individual cc reports pastes and refreshes all pivots closes and saves each cc report
Cells.Select
    Selection.Copy
    Workbooks.Open Filename:= _
        "F:\PH\DIR - RPS\Bus Plan & Support\Fin Mgmt\Reports\1314\Reports to Branch\01 Jul-13\6500 July Branch reports.xlsm" _
        , UpdateLinks:=3
    Sheets("GL Raw Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Windows("July Branch reports adjusted cp.xlsm").Activate
    ActiveWindow.SmallScroll Down:=63
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("6500 July Branch reports.xlsm").Activate
    Sheets("AP Raw Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Windows("July Branch reports adjusted cp.xlsm").Activate
    ActiveWindow.SmallScroll Down:=63
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("6500 July Branch reports.xlsm").Activate
    Sheets("CR Raw data").Select
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("General Ledger 6500").Select
    Range("C15").Select