Macro for copy & paste data from worksheets if path of file changes

[Animesh]

I have multiple workbook (around 60), which I have to copy & save in different folder as month changes but workbooks name remain same.

So, for Jan month path will be:- C:\2016\Jan\Victoria.xls, in Feb it will be:- C:\2016\Feb\Victoria.xls and so on.....Data also changes each month.

These 60 workbooks work as a input file for my working. There is a tab named "Journal" tab in each 60 workbooks and I have to copy data from "Journal" tab in each workbook and paste in a single worksheet; call it Working.xls.....

Now, instead of copying and pasting 60 files every time I wanted to apply simple link, but that would not work as path for both source and destination files changes every month.

So, I want to write a Macro to copy & paste data. But, I want to make a list of path for all input workbooks, write it in the Sheet1 of Working.xls and then write Macro in Sheet 2 and link that list with Macro as reference, so that each month when my input files are saved in new folder/path, I will just update the list of path in Working.xls and Macro would automatically copy data from new folder and paste in Working.xls(Sheet 2).

Please suggest a Macro to link the list and copy & paste data from my input files to Working.xls.

Any help would be greatly appreciated. Thanks in advance!!

[SamT]

But, I want to make a list of path for all input workbooks, write it in the Sheet1 of Working.xls and then write Macro in Sheet 2 and link that list with Macro as reference

The path is a pattern: "C:\" & Year & "\" & Mon & "\*.xls". . . You do not need a list.

The VBA Function "Dir" can return every file with the ".xls" Extension in that path.


CurrentYear = Format(Now, "yyyy") ' "Year" is not a valid variable name.
CurrentMon = Format(Now, "mmm")
PreviousYear = Format(DateAdd("y", Now, -1), "yyyy")
PreviousMon = Format(DateAdd("m", Now, -1), "mmm")



that would not work as path for both source and destination files changes every month.
Is "Working.xls" the destination file you mentioned? If it is not, what do you mean by "destination file?"

In my opinion, each month the "sheet1" should be a new sheet Named in the pattern "Working " & Year & "_" & Mon". You should change "Working " to suit you. Perhaps कार्य or प्रोसेसिंग