Pulling data from multiple sheets (and growing!) into a summary sheet

I've been a lurker for a long time, and I admire all of the Excel/VBA geniuses in here! I had to come out of lurkdom, make an account, hopefully to get some help from you all. I hope this makes sense... I included my sheet so you can see it.

New sheets are going to be created via using a macro that duplicates the Client Template tab. New client sheets are going to be added at will, so there is no set number of client sheets.

The Report sheet summarizes information from all client tabs. The fields it would pull would be Verification Requirement, Total Aging, Total Verifications, Favorable Amount and %, Unfavorable Amount and %, Open Amount and % from each client sheet (I have some coding already for a macro, which you can see, that pulls the client name from the tabs) - cells are A2, B10, C10, D10, E10, F10, G10, H10, I10.

It is only one line per client, coming from row 10 (because the Report worksheet is an ongoing summary) but only the most current month (row 10) of data would be pulled from each client tab.

It needs to copy the data, formatting, and the formulas associated with each cell. So from client sheets (i.e. the client template, and however many client sheets as they grow) TO the Report sheet.

So essentially: each client tab, these cells (A2, B10, C10, D10, E10, F10, G10, H10, I10) from row 10, go to the corresponding cells on the Report tab, each with their own row/line. The macro I made on the Report tab fills in the client name from each sheet (currently only the Client template sheet, but of course there will be more). As a new client sheet is created (dupliating the Client Template sheet), I would want the same cells/row to be copied from the client sheet to a row on the Report sheet.

Thank you so much in advance, it seems complicated but I know that the actual coding shouldn't be too hard!

Okay, I think I have some of it accomplished... Here is what I have so far... Please let me know any opinions.


Sub ListSheets()

Dim ws As Worksheet
Dim x As Integer

x = 12

Sheets("Report").Range("A12:Z112").Clear

For Each ws In Worksheets

If ws.Name <> "Report" And ws.Name <> "Schedule" And ws.Name <> "DataSource" Then

Sheets("Report").Cells(x, 1) = ws.Name
x = x + 1

ws.Select
Range("B10,C10,D10,E10,F10,G10,H10,I10").Copy
Sheets("Report").Select
Range("C" & x).Select
ActiveSheet.Paste


End If

Next ws



End Sub






If I can add on to my original question, I need to make a macro assigned to a button that the user can click on the template sheet to duplicate the entire sheet, including the format/formulas, but have the macros embedded in the template sheet copy over into every new subsequent sheet and have the macros apply to each individual sheet as opposed to applying to the template sheet...
For example, the template sheet has a macro to add a new row to a table. When I duplicate that sheet to create a client sheet (from the template), the macros come over but when the macro is used on that new sheet, it adds the new row to the original template sheet.