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

animeshpreetam

New Member
Joined
May 21, 2016
Messages
1
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!!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
I have multiple workbook (around 60)
If all the workbooks are named WB1 to WB60 then something like this would work.
Code:
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top