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
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