Consolidating data from many sheets into one sheet

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I need help consolidating data from a workbook with 31 tabs (individual staff members). The tab reports seat times online for teachers. Below is a sample tab. The sheet columns are shown in blue. The rows beneath the headers can vary wildly by teacher. I run some code and get a number showing unique days online for the teacher. It's in red and always located in H4 regardless of the tab. I would like to list each of the values in H4 on a separate worksheet in this workbook. I would then be able to take this list and insert it into a master report. Any help would be appreciated. A



BC D EFGH
3
TeacherCourseDateTime(hh:mm)IP AddressUnique Days
Teacher Name
*Home Page/eMail/Forum10/27/20190:02184.58.42.5810/27/2019
LANG Spanish II (19-36)10/27/20190:06184.58.42.5810/30/2019
*Home Page/eMail/Forum10/27/20190:00184.58.42.5811/4/2019
LANG Spanish II (19-36)10/27/20190:02184.58.42.58
*Home Page/eMail/Forum10/27/20190:01184.58.42.58
LANG Spanish II (19-36)10/27/20190:09184.58.42.58
*Home Page/eMail/Forum10/27/20190:00184.58.42.58
LANG Spanish II (19-36)10/27/20190:01184.58.42.58
*Home Page/eMail/Forum10/30/20190:00216.48.134.139
*Home Page/eMail/Forum11/4/20190:00216.48.135.139
Total0:21

<tbody>
</tbody>
</pre>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hello Andy0311,

I am not sure how you want the outputs to be arranged on the worksheet. Can you provide an example?
 
Upvote 0
If I interpreted the OP correctly, then

Code:
Sub t()
Dim sh As Worksheet, ws As Worksheet
Set sh = Sheets.Add
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sh.Name Then
            sh.Cells(Rows.Count, 1).End(xlUp)(2) = ws.Name
            sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = ws.Range("H4").Value
        End If
    Next
End Sub

Should give you the list.
 
Upvote 0
Hello Andy0311,

I am not sure how you want the outputs to be arranged on the worksheet. Can you provide an example?

Hi Leith,

The arrangement would be a simple column, nothing but a number. For example:

3
34
12
6
16

For all 31 tab values. Thanks again.

Andy
 
Upvote 0
Thanks so much for your help. I will try the code tomorrow and get back.

Andy
 
Upvote 0
I ran a test on just five sheets. I was expecting to see a list of values as such:

2
11
6
9
6

Those were the values in H4 on each of the sheets. On running the code I got:

Sheet1
Sheet2
Sheet4
Sheet3
Sheet5



<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

I ran the code from the first sheet. Did I do something wrong? Thanks for the help. A
 
Upvote 0
Hello Andy,

This should work for you. Add a new VBA Module to your workbook's VBA Project. Copy and paste the code into it.

Code:
Sub ConsolidateData()


    Dim NewWks  As Range
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        With ThisWorkbook.Worksheets
            Set NewWks = .Add(After:=.Count)
            Set Rng = NewWks.Range("A1")
            
            For Each Wks In .Worksheets
                If ObjPtr(Wks) <> ObjPtr(NewWks) Then
                    Rng.Value = Wks.Range("H4").Value
                    Set Rng = Rng.Offset(1, 0)
                End If
            Next Wks
        End With
        
End Sub
 
Upvote 0
I ran a test on just five sheets. I was expecting to see a list of values as such:

2
11
6
9
6

Those were the values in H4 on each of the sheets. On running the code I got:

Sheet1
Sheet2
Sheet4
Sheet3
Sheet5



<TBODY>
</TBODY>

I ran the code from the first sheet. Did I do something wrong? Thanks for the help. A

You have to have a value in H4 of each sheet for it to list them. The sheet names were included in the code to show where the values came from. If H4 of each sheet contains a value then you should get something like :

Sheet1 val1
Sheet2 val2
Sheet3 val3
 
Upvote 0
Hello Andy,

This should work for you. Add a new VBA Module to your workbook's VBA Project. Copy and paste the code into it.

Code:
Sub ConsolidateData()


    Dim NewWks  As Range
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        With ThisWorkbook.Worksheets
            Set NewWks = .Add(After:=.Count)
            Set Rng = NewWks.Range("A1")
            
            For Each Wks In .Worksheets
                If ObjPtr(Wks) <> ObjPtr(NewWks) Then
                    Rng.Value = Wks.Range("H4").Value
                    Set Rng = Rng.Offset(1, 0)
                End If
            Next Wks
        End With
        
End Sub

Hi Leith,

I ran a test on 8 tabs (rather than the 31 I will ordinarily use. I ran the code from sheet 1. I got this error message: 'Compile error: Method or data member not found'. Here is the code returned:

Code:
[COLOR=#ffff00]ub ConsolidateDaysOnlineData()[/COLOR]
Sub ConsolidateDaysOnlineData()


    Dim NewWks  As Range
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        With ThisWorkbook.Worksheets
            Set NewWks = .Add(After:=.Count)
            Set Rng = NewWks.Range("A1")
            
            For Each Wks In [COLOR=#0000ff].Worksheets[/COLOR]
                If ObjPtr(Wks) <> ObjPtr(NewWks) Then
                    Rng.Value = Wks.Range("H4").Value
                    Set Rng = Rng.Offset(1, 0)
                End If
            Next Wks
        End With
        
End Sub
[code]

The Sub line was filled in in yellow (couldn't do it here, sorry), and .Worksheets was in blue. Before I ran the code, I ran a macro which names each sheet with a person's last name. Would removing the names from my tabs and using Sheet 1, Sheet 2, etc and rerun the code resolve the problem? Also, will the code work any number of tabs (worksheets). The test I ran used only 8 to save time. I would ordinarily use use the code for my 31 staff members. Thanks again for helping me. 

Andy
 
Upvote 0
You have to have a value in H4 of each sheet for it to list them. The sheet names were included in the code to show where the values came from. If H4 of each sheet contains a value then you should get something like :

Sheet1 val1
Sheet2 val2
Sheet3 val3

I rechecked to make sure there was a value in H4 in every workbook sheet. I'm still not getting the desired result:

Sheet1 val1
Sheet2 val2
Sheet3 val3
etc

Any ideas? Thanks for all your help, though. A
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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