PDA

View Full Version : Relative address rather than absolute address in VBA code (macro)



ceepee
09-08-2013, 08:31 PM
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

snb
09-09-2013, 01:42 AM
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.

ceepee
09-09-2013, 03:09 PM
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.

snb
09-09-2013, 03:21 PM
There's a myriad of examples in this and other fora.

ceepee
09-09-2013, 03:49 PM
There's a myriad of examples in this and other fora.

Thank you