Pull cell data from multiple sheets into a list on a master sheet

[dustinjmangum]

Hello,

This is my first post so I will try my best to explain what I am trying to do. For my job I have created a sheet that has a checklist for each "customer" that I have in my care. I work for a mortuary. Each person would have there own sheet. What I am wondering is if there is a way to automatically pull the data that is in a certain cell on each sheet into a list on a master sheet. For example in the screenshots below I want to pull the information from J4, and have it automatically be placed onto the summary sheet in a list under "Deceased Name." Is there away to tell excel to reference J4 of every new inserted sheet? This is my first post, so I hope I am making sense, I appreciate any and all help...

 

[Herakles]

Hello,

This is my first post so I will try my best to explain what I am trying to do. For my job I have created a sheet that has a checklist for each "customer" that I have in my care. I work for a mortuary. Each person would have there own sheet. What I am wondering is if there is a way to automatically pull the data that is in a certain cell on each sheet into a list on a master sheet. For example in the screenshots below I want to pull the information from J4, and have it automatically be placed onto the summary sheet in a list under "Deceased Name." Is there away to tell excel to reference J4 of every new inserted sheet? This is my first post, so I hope I am making sense, I appreciate any and all help...

If I was you I would create a macro, or ask someone here to help you, that creates the new sheet after you have filled in a new row on the master sheet. The macro could name the new sheet and fill in data from the master sheet.

 

[Sanjeev1976]

Hi Dustin,

On a copy of your file, you can create "Master Sheet" at the beginning of the file and add the below code to the sheet code and see how it goes :

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long

calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False

n = 2
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            n = n + 1
               
                If Me.Cells(n, 1) <> "" Then Me.Cells(n, 1) = wSheet.Range("J4").Value
                If Me.Cells(n, 2) <> "" Then Me.Cells(n, 2) = wSheet.Range("J5").Value
                If Me.Cells(n, 3) <> "" Then Me.Cells(n, 3) = wSheet.Range("J6").Value
                If Me.Cells(n, 4) <> "" Then Me.Cells(n, 4) = wSheet.Range("C4").Value
                
        End If
    Next wSheet
   
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub

Make necessary changes to suit your requirements.