How to Copy Data from Multiple sheets into one Master sheet in same Excel file




Copy data from multiple sheets into Master sheet in Workbook

You can track work of your team members using excel sheet template. If you want to track the major activities or major issues worked by your team members using excel then the attached sample excel will be handy. You can make little tweaks to this workbook and use it for your tracking purpose.

How this sample excel works:

-I have created sheet for each member to update the activity details in specific format.

-Once the members save their data, I can view the details in Mater sheet.

-When workbook is opened the Macro runs and updates data from each sheet into Master sheet and I have all the consolidated information in one Master sheet.

 

Features:

– Each member can update their details in allocated sheets.

-Data is entered in fixed format.

-Auto fill or list option provided wherever required. Say in Master sheet I would need to know who is working on this activity, so each members name is updated automatically when he enters the activity in his sheet.

-Later if the activity is updated for status or nay other detail is changed then it gets updated automatically in Master sheet. So the Master sheet is always up to date with activity status.

 

Check this video to see how it works-

 

Macro code to copy data from multiple sheets into Master Sheet (In this example data is copied from 6 sheets):

—————————————————————————————–

Sub Combine()

‘Define variables
Dim Sht As Worksheet
Dim Sh1 As Worksheet
Dim num As String
Dim numVar As String
Dim masterNum As String

‘Open workbook and start reading data from each worksheet)
For Each Sht In ActiveWorkbook.Worksheets
Set Sh1 = ActiveWorkbook.Sheets(“Master”)
‘Data from row number 5 should be copied into Master sheet

num=”5″

‘The row number is saved in Master sheet under column L, M, N, O ,P and Q. This acts pointer to start copying from.
If Sht.Name = “John” Then
num = Sh1.Range(“L2”).Value
End If
If Sht.Name = “Kevin” Then
num = Sh1.Range(“M2”).Value
End If
If Sht.Name = “Alton” Then
num = Sh1.Range(“N2”).Value
End If
If Sht.Name = “Mark” Then
num = Sh1.Range(“O2”).Value
End If
If Sht.Name = “Steve” Then
num = Sh1.Range(“P2”).Value
End If
If Sht.Name = “Pipes” Then
num = Sh1.Range(“Q2”).Value
End If
‘This is count for Master sheet. The sheet should be appended from this count.
masterNum = Sh1.Range(“K2”).Value

While Sht.Range(“A” + num).Value <> “”
If Sht.Name <> “Master” Then

Sht.Select
Range(“A” + num).Copy
Sheets(“Master”).Select
Range(“A” + masterNum).Value = “=” + Sht.Name + “!” + “A” + num

Sht.Select
Range(“B” + num).Copy
Sheets(“Master”).Select
Range(“B” + masterNum).Value = “=” + Sht.Name + “!” + “B” + num

Sht.Select
Range(“C” + num).Copy
Sheets(“Master”).Select
Range(“C” + masterNum).Value = “=” + Sht.Name + “!” + “C” + num

Sht.Select
Range(“D” + num).Copy
Sheets(“Master”).Select
Range(“D” + masterNum).Value = “=” + Sht.Name + “!” + “D” + num

Sht.Select
Range(“E” + num).Copy
Sheets(“Master”).Select
Range(“E” + masterNum).Value = “=” + Sht.Name + “!” + “E” + num

Sht.Select
Range(“F” + num).Copy
Sheets(“Master”).Select
Range(“F” + masterNum).Value = “=” + Sht.Name + “!” + “F” + num

Sht.Select
Range(“G” + num).Copy
Sheets(“Master”).Select
Range(“G” + masterNum).Value = “=” + Sht.Name + “!” + “G” + num

Sht.Select
Range(“H” + num).Copy
Sheets(“Master”).Select
Range(“H” + masterNum).Value = “=” + Sht.Name + “!” + “H” + num

Sht.Select
Range(“I” + num).Copy
Sheets(“Master”).Select
Range(“I” + masterNum).Value = “=” + Sht.Name + “!” + “I” + num

num = num + 1

Sh1.Range(“K2”).Value = CInt(masterNum) + 1

If Sht.Name = “John” Then
Sh1.Range(“L2”).Value = num
End If
If Sht.Name = “Kevin” Then
Sh1.Range(“M2”).Value = num
End If
If Sht.Name = “Alton” Then
Sh1.Range(“N2”).Value = num
End If
If Sht.Name = “Mark” Then
Sh1.Range(“O2”).Value = num
End If
If Sht.Name = “Steve” Then
Sh1.Range(“P2”).Value = num
End If
If Sht.Name = “Pipes” Then
Sh1.Range(“Q2”).Value = num
End If
masterNum = Sh1.Range(“K2”).Value

Else
num = num + 1

End If
Wend
Next Sht

End Sub

——————————————————————————

VB script to update auto data in respective sheets (like updating name and current date):

——————————————————————————

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim userName(6) As String
Dim sheetName As String

userName(1) = “John”
userName(2) = “Kevin”
userName(3) = “Alton”
userName(4) = “Mark”
userName(5) = “Steve”
userName(6) = “Pipes”

sheetName = ActiveSheet.Name
If Target.Column = 7 Then
Select Case sheetName
Case Is = userName(1)
Target = userName(1)
Case Is = userName(2)
Target = userName(2)
Case Is = userName(3)
Target = userName(3)
Case Is = userName(4)
Target = userName(4)
Case Is = userName(5)
Target = userName(5)
Case Is = userName(6)
Target = userName(6)
End Select
End If

If Target.Column = 2 And Target.Address = ActiveCell.Address Then
Target = Format(Date, “dd/mm/yyyy”)
End If
If Target.Column = 6 And Target.Address = ActiveCell.Address Then
Target = Format(Date, “dd/mm/yyyy”)
End If

End Sub

——————————————————————————-