nadeem has attended:
Excel Advanced course
Macro to copy and paste data from file saved in folders
Hi,
I recently attended the excel advanced course and I was shown how to record a Macro by Doug. I am confused on how to copy data from files saved in different monthly folders i.e May-13, June-13, July-13 etc and paste into my financial scorecard, I would like the macro to look up future months and automatically pick up the data and paste it into my financial scorecard.
I was given this code by Doug,he showed me how to to do data edit links for files saved in different folders i.e May-13, Jun-13, July-13.
Can you please help?
Sub July()
Dim monthname As String
montname=InputBox("Enter new month"
chDir"Z:\excel 2007 Advanced course files\demo files\" & monthname Activeworkbook.ChangeLink Name:=
"Z:\Excel 2007 Advanced course files\demo files\" & monthname& "\Northjuly.xlsx
"Type_
:=xlExcelLinks
End Sub
RE: Macro to copy and paste data from file saved in folders
Hi Nadeem
Thanks for getting in touch. I just wanted to clarify the purpose of the macro. The code you have at present would replace any linked formulas in the current workbook with the new month entered.
Is this what you want?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Macro to copy and paste data from file saved in folders
Hi Gary,
That is correct I would like the macro to link my financial scorecard to the new monthly file, i,e June-13 to link to July-13, this will save me time on various files which are linked to my scorecard.
Would you be able to give me a step by step guide on how to do that?
RE: Macro to copy and paste data from file saved in folders
Hi,
I have created a macro to automatically update my link to the next month which seems to be working. Isit also possible to open the source file for the current month using the smae macro or is that not possible?
Any help would be appreciated!
Thanks
Sub ki_npi_complete2()
'
' ki_npi_complete2 Macro
'
' Keyboard Shortcut: Ctrl+k
'
Dim monthname As String
monthname = InputBox("Enter new month")
ChDir "S:\Finance\Report 2013\2013 Monthend\05-May\US Submission"
ActiveWorkbook.ChangeLink Name:="UK 05-13 Key Initiatives.xlsm", NewName:= _
"S:\Finance\Report 2013\2013 Monthend\06-May\US Submission\UK 06-13 Key Initiatives.xlsm" _
, Type:=xlExcelLinks
Range("I9").Select
Workbooks.Open Filename:= _
"S:\Finance\Report 2013\2013 Monthend\05-May\US Submission\UK 05-13 Key Initiatives.xlsm" _
, UpdateLinks:=3
Application.Goto Reference:="'Const $'!R[-20]C[32]:R[1048522]C[32]"
Windows("UK Scorecard June 2013 Final.xlsx").Activate
End Sub
RE: Macro to copy and paste data from file saved in folders
Hi Nadeem
Sorry for delay in getting back.
Your code looks ok but will need adjusting every month to change the month name and month folder name.
To open a file for the current month you could format the current date. For example, if May is the current month the following would open your 05-13 Initiatives file.
Workbooks.Open Filename:="S:\Finance\Report 2013\2013 Monthend\05-May\ _
US Submission\UK" & Format(Date, "mm") & "-13 Key Initiatives.xlsm"
To automatically open the file of the current month you may need more general subfolder names. May instead of 05-May. Then you could include Format(date,"mmmm") to find current month folder name.
Hope that helps for now. I'm training onsite for a few days but will check if another trainer is available to help.
Regards
Doug Dunn
Best STL
Read more: https://www.stl-training.co.uk/post-33877-macro-copy-and-paste.html #ixzz2XtBed0bX
