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>

 

[JLGWhiz]

I am baffled. In my test setup, I get the list created with sheet name in column A and values in column B. My test setup consists of three sheets with values in cell H4, the code is run from module1 of the same workbook.

Is the data in your worksheets in a named table?

 

[Andy0311]

I am baffled. In my test setup, I get the list created with sheet name in column A and values in column B. My test setup consists of three sheets with values in cell H4, the code is run from module1 of the same workbook.

Is the data in your worksheets in a named table?

No. My worksheet data is not in a named table. A

 

[JLGWhiz]

Well, I am at a loss as to why the values are not showing in column B. Can you copy the code from your code module and paste it intothe 'Reply' pane so I can see if there is a difference between that code and my post # 3 code?

 

[Andy0311]

Well, I am at a loss as to why the values are not showing in column B. Can you copy the code from your code module and paste it intothe 'Reply' pane so I can see if there is a difference between that code and my post # 3 code?

I will do that. Thanks.

 

[Andy0311]

Well, I am at a loss as to why the values are not showing in column B. Can you copy the code from your code module and paste it intothe 'Reply' pane so I can see if there is a difference between that code and my post # 3 code?

Here's the 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
[code]

I run it from the first tab (Worksheet1). Thanks again for your help. A

 

[Andy0311]

I just ran a test with six sheets. It returned "Sheet1" in A2 on Sheet7. Nothing else. Thanks.

 

[JLGWhiz]

I just ran a test with six sheets. It returned "Sheet1" in A2 on Sheet7. Nothing else. Thanks.

The code should be installed in a public module like module1, but I ran it from sheet1 of my file and it worked OK. I have no idea why it is not working for you and I cannot duplicate the results you are getting, so I will disconnect from this thread and let somebody else smarter than me try to help you.
Regards, JLG