How to automatically copy & paste data from several worksheet to one worksheet, if the path of source and destination file changes

[animeshpreetam]

I have multiple workbook (around 60), which I have to copy & save in different folder as month change but workbooks name remain same. So, for Jan month path will be:- C:\2016\Jan\WB1.xls, in Feb it will be:- C:\2016\Feb\WB1.xls and so on.....
Data also changes each month. There is 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 Upload.xls.....Now instead of copying and pasting every time I wanted to apply simple link, but that would not work as path for both source and destination file changes every month. So, I want to write Macro now. But, I want to make a list of path for all workbooks and use it as reference in Macro, so that next month when my files are saved in new folder, I will just update the list of path and Macro would automatically pick data from new folder and save it in Upload worksheet. Is it possible? If reference is not possible this way then can anyone suggest simple Macro to copy & paste data or is there any way to link my Upload.xls to source files so that even path changes for source files, my worksheet Upload.xls also changes without macro. Thanks in advance!!

 

[JLGWhiz]

I have multiple workbook (around 60)

If all the workbooks are named WB1 to WB60 then something like this would work.

Sub copyStuff()
Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Workbooks("Upload.xls").Sheets(1) 'Edit sheet name
	For i = 1 To 60
		Set wb = Workbooks.Open("C:\2016\"[COLOR="#B22222"] & Format(Date - 28, "mmm") &[/COLOR] "\WB" [COLOR="#B22222"]& i &[/COLOR] ".xls")
		set sh2 = wb.Sheets("Journal")
		'Code to copy stuff here
	Next
End Sub

the red font are variables that accomodate both your month and workbook name change in the path, if the the files are all name WB#. If the files do not all have the same basic name with a numerical suffix, then you could list the varying paths on a second sheet in column A and use a code line like:

For i = 1 To Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Set wb = Workbooks.Open(Sheets(2).Cells(i, 1).Value)

Your For i statement would be dynamic so that if you add rows to or delete rows from your list, it would still still cover the full range of workbooks. It could present problems if you only clear contents to delete a file name, because it would leave blank rows.

Hope this helps.