Copying worksheets into one

[mollysnuffy]

I'm trying to copy specific worksheets into one called Summary that I have already created (worksheets are Bathroom, Plumbing, Tiling, Plastering, Labour-Sundries). I need it to only copy the rows that have a 1 in column A from each sheet, and not copy the header row. I would also like it to copy the formatting and I would like it to start from row 10 in the Summary sheet so that I can put calculations in at the top.

Please can someone help I've been trying different examples that I've adapted for hours with no success! Thank you

[pike]

Hi mollysnuffy
What do you have so far

[mollysnuffy]

This is what I have so far but cant seem to get it to only look at column A where the value is 1. The code also seem quite long winded to the other examples I have found on the net but it kind of works (also haven't changed the sheet to be "Summary"). It is several few year since I dabbled with VB and other programming so I'm not 100% sure what does what, although some of what I was taught is making sense! Would appreciate any help.

Thank you.

[JBeaucaire]

This is how I would do that. It's a worksheet_activate macro, so each time you pull up the SUMMARY sheet, it will re-gather the data from the other sheets so it's always accurate.

Please Login or Register  to view this content.

This macro is installed into the sheet module.

[mollysnuffy]

Thank you so much for this. It does exactly what I want it to.

I would also like to be able to run the macro to update the "Summary" sheet whilst I'm working on it rather than just on opening.

I also need to add other worksheets that wont be in the same format and dont need the data to import into "Summary", how do I tell it to only look at Bathroom, Plumbing, Tiling, Plastering, Labour-Sundries.

Thank you once again. Alison

[JBeaucaire]

1) You would have to go to the other sheets to change the values and come back, so SUMMARY sheet will always be accurate within the workbook itself.

2) This macro can replace the one in the workbook to fix the event to only the sheets named:

Please Login or Register  to view this content.

1) Right-click on the SUMMARY sheet tab
2) Select VIEW CODE
3) Delete the code in the window that appears
4) Paste in the code given above
5) Close the editor and save your workbook.

[mollysnuffy]

Thank you once again for your help. I have now included other worksheets and a letter template in the workbook to enable me to get quotes out to customers much quicker.

I really appreciates the help.

Kind regards Alison

[JBeaucaire]

My pleasure.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)