Consulting

Results 1 to 5 of 5

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

  1. #1

    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

    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
    Last edited by Aussiebear; 09-09-2013 at 06:11 PM. Reason: Added tags to code section

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,778
    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.

  3. #3
    Quote Originally Posted by snb View Post
    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.

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,778
    There's a myriad of examples in this and other fora.

  5. #5
    Quote Originally Posted by snb View Post
    There's a myriad of examples in this and other fora.
    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •