Excel-Macro to sumup data from multiple workbooks to Master xlsx

Closed
Unokashi Posts 3 Registration date Thursday December 11, 2014 Status Member Last seen December 19, 2014 - Dec 11, 2014 at 03:58 AM
Unokashi Posts 3 Registration date Thursday December 11, 2014 Status Member Last seen December 19, 2014 - Dec 19, 2014 at 02:12 AM
Hi All,

Can you please help me.
Scenario:
1. There is a common template which has some tasks listed in columns against which the team members will update the time taken (effort) for each task in the entire month.
2. There are different levels of team members (Entry, Intermediate, Specialist, etc.).
3. The template has multiple sheets, but all the sheets have same template.
4. The template has the common tasks listed in the Columns (Say A25 to A156) and on the rows there are these levels mentioned under which depending on his/ her level, the team member enters the time (effort) for each task [so if he/ she is and Entry, they will enter values (Effort) in B25 to B150 cells]. There are chances that the cells can be empty for few of the tasks also. The Range may vary, so in the solution it would be helpful if I get an option to enter the range that I want to consolidate.

Example
Tasks SPE INT ENT COR
Task 1 2
Task 2 4
Task 3 5
Task 4
Task 5 12
Task 6 22
Task 7 1

5. Each of the team member submits one excel work book with the entries for the entire month based on his level, so I will have multiple such work books in the name of each team member.

The need:
I have a master file which has the same tasks listed in columns and same levels mentioned in the rows (rather the same template). I need to consolidate by adding the data from multiple cell ranges (say E11:F13, E17: F18, D25:M165). The below code is currently doing only for one set of cell range.

A macro with the following would help:
1. An option to select multiple excel sheets rather than path hardcoded.
2. If there are entries in other sheets (P2, P3, etc) consolidate the same in the respective Master sheet (P2, P3, etc).
3. Everytime I run the Macro, it has to wipe previous values of the cell and consolidate.

Currently I have the following Macro:

'*******************************************************************************************
'Macro: To Consolidate the Output data into the Master Excel sheet
'*******************************************************************************************
Option Private Module
Option Explicit

Sub combine_data()
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As String
Dim SumTemplate As String
Dim myWS As Worksheet
Dim sumWS As Worksheet
Dim FilesToOpen

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Define folders and filenames
MyPath = "C:\Output_Data\Current_Month\"
SumPath = "C:\Output_Data\Current_Month\Consolidated\"
MyTemplate = "*.xls*" 'Set the template.
SumTemplate = "Output*.xls*"

'Open the template file and get the Worksheet to put the data into
SumName = Dir(SumPath & SumTemplate)
Workbooks.Open SumPath & SumName
Set sumWS = ActiveWorkbook.Worksheets("P1")
Sheets("P1").Unprotect Password:="password" 'Unprotect the sheet
sumWS.Range("D25:M165").ClearContents 'Clear contents of the Master Template in the specified range

'Open each source file, copying the data from each into the template file
MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
Do While MyName <> ""
'Open the source file and get the worksheet with the data we want.
Workbooks.Open MyPath & MyName
Set myWS = ActiveWorkbook.Worksheets("P1")
'Copy the data from the source and paste at the end of Summary sheet
myWS.Range("D25:M165").Copy
sumWS.Range("D25:M165").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
'Close the current sourcefile and get the next
Workbooks(MyName).Close SaveChanges:=False 'close
MyName = Dir 'Get next file
Loop

'Now all sourcefiles are copied into the Template file. Protect the sheet, Close and save it
Sheets("P1").Protect Password:="password"
Workbooks(SumName).Close SaveChanges:=True

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "Done with Consolidating the Data! Proceed to the following path to Open the file C:\Output_Data

\Current_Month\Consolidated\Output_Data*.xls*", vbOKOnly, "Consolidate Data"

End Sub

2 replies

Unokashi Posts 3 Registration date Thursday December 11, 2014 Status Member Last seen December 19, 2014
Dec 18, 2014 at 12:54 AM
Can anybody please help me.
0
Unokashi Posts 3 Registration date Thursday December 11, 2014 Status Member Last seen December 19, 2014
Dec 19, 2014 at 02:12 AM
Can anybody please help me.
0