Hi,
I have 32 different worksheets in a master workbook. The 32 different sheets are holding similar data about 32 seperate entities. Each of these 32 entities provides me with a workbook which has multiple worksheets. Sheet 2 of each of these entity workbooks has the data I need copied from the entity workbook to my masterworkbook. The data from entity x workbook should go to the entity x worksheet in the Master workbook.
I am using the following code but am having some problems;
I intend to
activate the Masterworkbook (ThisWorkbook) first.
Go to entity 1 worksheet, use the name of the worksheet to concactenate the path for the entity 1 workbook,
check if the entity 1 workbook is there in the folder,
if the entity 1 workbook is there copy data in Range (A6, t14)from Sheet 2 of entity 1 workbook and then paste the same data in entity 1 worksheet in the master workbook
otherwise Msg box message = "Not Found"
go to next entity worksheet in the master workbook and repeat
Sub link_files()
Dim WIPSWS As ThisWorkbook
Dim DepStaff As Worksheets
Dim WIPDEPT As Workbook
Dim WBPath As String
WBPath = "B:\2014 Budget\WIP 2013"
For Each DepStaff In WIPSWS
Set WIPDEPT = Workbooks.Open(WBPath & "\" & DepStaff.Name & ".xlms")
Active Workbook.Sheet2.Activate
Range(A6, T9).Copy
I have 32 different worksheets in a master workbook. The 32 different sheets are holding similar data about 32 seperate entities. Each of these 32 entities provides me with a workbook which has multiple worksheets. Sheet 2 of each of these entity workbooks has the data I need copied from the entity workbook to my masterworkbook. The data from entity x workbook should go to the entity x worksheet in the Master workbook.
I am using the following code but am having some problems;
I intend to
activate the Masterworkbook (ThisWorkbook) first.
Go to entity 1 worksheet, use the name of the worksheet to concactenate the path for the entity 1 workbook,
check if the entity 1 workbook is there in the folder,
if the entity 1 workbook is there copy data in Range (A6, t14)from Sheet 2 of entity 1 workbook and then paste the same data in entity 1 worksheet in the master workbook
otherwise Msg box message = "Not Found"
go to next entity worksheet in the master workbook and repeat
Sub link_files()
Dim WIPSWS As ThisWorkbook
Dim DepStaff As Worksheets
Dim WIPDEPT As Workbook
Dim WBPath As String
WBPath = "B:\2014 Budget\WIP 2013"
For Each DepStaff In WIPSWS
Set WIPDEPT = Workbooks.Open(WBPath & "\" & DepStaff.Name & ".xlms")
Active Workbook.Sheet2.Activate
Range(A6, T9).Copy
