VBA Copy certain rows from multiple sheets (not all) plus copy value of one cell

[ExcelHelpNeeded99]

Hi there,

Hoping someone can help.
I have a workbook with hundreds of sheets and I want to copy only for the ones with a "," in the sheet name. (These are the sheets with peoples names, one sheet per person)
All sheets are formatted the exact same way but the headers for row 2 through 33 are in column A with data in column C but on row 34 they switch to header being in row 34 with data in rows 35 through 52.

On the sheet called "Master2" I want to repeat the formal name in column A and show every event attended by that person on a separate row. Then it goes to the next sheet and does the same and so on.

Please can you help with the code?

Thanks

DATA on Multiple SHEETS	Column A	Column B	Column C	Column D
Row 5	Formal Name		Miguel Smith
Row 34	Date	Note	Speaker	Topic
Row 35 through 52	5/12/16	xyz	Y	Politics
	3/31/17	abc	n	n/A

<tbody>
</tbody>

Master 2	Column A (Formal Name)	Column B (Date)	Column C (Note)	Column D (Speaker)	Column E (Topic)
Row 2	Miguel Smith	5/12/16	xyz	Y	Politics
Row 3	Miguel Smith	3/31/17	abc	n	n/a
Row4	John Roberts	...	...	...	...

<tbody>
</tbody>

 

[JLGWhiz]

I am not really sure that I understand your sheet layouts nor what you want copied, but you can try the code and post back with what needs to be fixed.

Sub t()
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Master2" And sh.Name Like "*,*" Then
            lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
            Sheets("Master2").Cells(Rows.Count, 1).End(xlUp)(2) = sh.Range("C5").Value
            sh.Range("A35:D" & lr).Copy Sheets("Master2").Cells(Rows.Count, 1).End(xlUp).Offset(, 1)
        End If
    Next
End Sub

 

[ExcelHelpNeeded99]

Thanks JLGWhiz but I cant seem to get that to work.
On all sheets with a “,” I want the value from cell C5 (Persons name)
I then want the data from the rows A35 through D52 which is details of each event the person has attended.

I would like to format it in the master2 sheet so that there is one row per event and so each persons name will be repeated for 18 rows (I can always delete any blank event rows).

Persons name (Column A) Event details (Columns B,C,D,E)