Consolidating data from many sheets into one sheet

[Andy0311]

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

B	C	D	E	F	G	H
						3
Teacher	Course	Date	Time(hh:mm)	IP Address		Unique Days
Teacher Name
	*Home Page/eMail/Forum	10/27/2019	0:02	184.58.42.58		10/27/2019
	LANG Spanish II (19-36)	10/27/2019	0:06	184.58.42.58		10/30/2019
	*Home Page/eMail/Forum	10/27/2019	0:00	184.58.42.58		11/4/2019
	LANG Spanish II (19-36)	10/27/2019	0:02	184.58.42.58
	*Home Page/eMail/Forum	10/27/2019	0:01	184.58.42.58
	LANG Spanish II (19-36)	10/27/2019	0:09	184.58.42.58
	*Home Page/eMail/Forum	10/27/2019	0:00	184.58.42.58
	LANG Spanish II (19-36)	10/27/2019	0:01	184.58.42.58
	*Home Page/eMail/Forum	10/30/2019	0:00	216.48.134.139
	*Home Page/eMail/Forum	11/4/2019	0:00	216.48.135.139
	Total		0:21

<tbody>
</tbody>

</pre>

 

[Leith Ross]

Hello Andy0311,

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

 

[JLGWhiz]

If I interpreted the OP correctly, then

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.

 

[Andy0311]

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

 

[Andy0311]

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

Andy

 

[Andy0311]

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:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Sheet1[/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[/TR]
[TR]
[TD]Sheet5


[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Leith Ross]

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.

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

 

[JLGWhiz]

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:

[TABLE="width: 64"]
<TBODY>[TR]
[TD="width: 64"]Sheet1
[/TD]
[/TR]
[TR]
[TD]Sheet2
[/TD]
[/TR]
[TR]
[TD]Sheet4
[/TD]
[/TR]
[TR]
[TD]Sheet3
[/TD]
[/TR]
[TR]
[TD]Sheet5


[/TD]
[/TR]
</TBODY>[/TABLE]

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

 

[Andy0311]

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.

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:

[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

 

[Andy0311]

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