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

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

Code:

---

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

---

It would be nice if you used the code code tags to wrap your VBA code in.

You'd better trim the code first, avoiding the 'select' and 'activate' methods.

Quote:

---

Originally Posted by snb

It would be nice if you used the code code tags to wrap your VBA code in.

You'd better trim the code first, avoiding the 'select' and 'activate' methods.

---

Thanks for your reply however I am not advanced enough in VBA to do what you are suggesting, the select and activate methods are staright from an Excel macro and so if there is a way that I can trim this code I am not aware of it.

There's a myriad of examples in this and other fora.

Quote:

---

Originally Posted by snb

There's a myriad of examples in this and other fora.

---

Thank you