VBA to copy all rows in any sheet with 1 in column A and copy entire row to specific sheet

Hi,
I have a work book with multiple tabs that depending on the project will have different names.

I would like to summarise the entire row of any sheet/tab to a another sheet/tab called Take off.

Because the names of the sheets can change depending on the project, I was thinking that a search of the entire note book for the "1" in column A was the best approach.


Ultimately any row with 1 within column A would be copied into the "Take off" sheet.


Appreciate your help.

Lui

Alan,
thank you very much. The macro does work, however if I run it again, it doesn't delete the previous results it just adds them to the bottom. Is there a away to delete the previous results when running the macro again. Please keep in mind that at the bottom of the take off page I wanted to sum the results.

I threw some figures into the attached.

Appreciate any further assistance.

Alan,
again thanks.

Don't want to be a pain, but a couple of things happening.

In the Take off tab, when I run the macro the column heading I have along row 1 disappear. Would be great if the column headings didn't get deleted.
Also in the take off tab, is there a way maintain a row summing the results as per my last attachment, that won't get deleted if the rows go past the formula row.

I hope I'm making sense.

Again thanks for your time.

Alan,
unfortunately it still deletes the top row text.

It also now keeps the formula row in place, but the formula is no longer pushed to the bottom of the list and whilst he formula text format stays, the actual formula disappears.

Again thanks.

Alan,
got a run time error "1004".


I have attached the file again.

Thanks.

Alan,
just getting back to this now.

if there are more that 48 lines being copied then the formula on row 48 is overridden.

Can the formula just be added to one row after the last row, wherever the last row ends up?