Macro button copy specific rows from all worksheets to a summary sheet

Hi everyone at Excel Forum!

I have been trying to solve this problem for weeks! I am trying to create a macro (to assign to a button) that will, for all worksheets in the workbook except "summary" sheet, looks at the rows on or after row 14 and if there is a value in the "C" column in the row then to copy that row from column 'B' to column 'G' and paste it into the "summary" sheet starting at row 5 and offset a few columns to the right, e.g. column 'D' to column 'I'.

Additionally there are some "static-location" cells with information associated to those rows that I want to copy into each row copied into summary sheet.

For example:
Sheet 1 = Summary
Sheet 2 = ProjectOne

On the sheet "ProjectOne" in cell 'A1' is the project name, in cell 'B1' is the project date, and in cell 'B3' is the project manager; and there are values in cells "B14:G70" (i.e. rows 14 to 70 and from column B to G).

When I run the macro, the summary sheet would copy "B14:G70" from "ProjectOne" and paste them into "Summary" at "D5:I61" and then copy cell 'A1', 'B1', and 'B3' from "ProjectOne" and paste them next to each of those copied rows respectively in columns 'A', 'B', and 'C'.

Then it would repeat for every worksheet in the workbook.

Any help is greatly appreciated!! Thank you!

Please attach a sample. Go to advance then attachment

Hi AB33!

I have made a sample file that demonstrates what I am trying to do. I added colors to show the mapping of the cells.

What you see on the summary page is an example of the end result. I didn't mention it in my original post but I would also need it to clear all cells every time you click the button (so that it replaces the old summary with updated info).

Example Consolidation for CME.xlsm

Try the attached

CME,
You are welcome!
PM me if you have any issue with or want to adjust the code.

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

Done!

Will PM you if any additional help is needed. Thank you so much again AB33!

Hi AB33, as I PM'd you:

Attached is the real file, I am very sorry I didn't just do this the first time!

- There are some conditional formatting setups on the worksheets that seem to be affecting the summary file during the consolidation, I don't need them to be preserved after the copy, just values are fine like the code says but for some reason it is still affecting it.

- I can't get the "static data" to map properly; as you'll see in the file there are several more to transfer than the sample file and some of them go after the pasted "row"

TNA Tester Sheet vAB33.xlsm

Thank you so much!

cme,
You have two choices of copying, one with formatting and other values only. I did copy only. I think the issue may be clear, instead of clear contents. I now have amended the code, but not sure on formatting.

Again, you are awesome AB33!

I see what you mean about the formatting.
I believe I have targeted the issue, it is the conditional formats that I have set up on the non-summary sheets ("TNA" sheets) there are meant to be only 3 basic conditional formats that highlight the rows in green when complete, red when overdue, and yellow when there is a comment. It seems somehow those got duplicated on a MASS scale which I just noticed (and explains why the sheet is so slow) and have cleaned it up.

If you look at the below version, I have reset all the formats to their proper arrangement (none on the summary, 3 on each of the TNAs). You'll see this from before and after hitting the button: the code is copying the value+format which by default copies the conditional formatting with it.

So I believe if the copy/paste is switched to values only it will be resolved!

TNA Tester Sheet vAB33 v3 CLEANED C-FORMAT.xlsm

Okay change that single line with two lines

Please Login or Register  to view this content.

Ok, made that switch and all the formatting is fixed. Great.

Almost there!

Here is a screen shot of something that is happening in some circumstances:
Troubleshooting issue 1.jpg

Sometimes the information is being pasted separately in the wrong rows. I believe this has something to do with the checkboxes. Those checkboxes are linked to a hidden column "C" on the non-summary sheets and by default column C is blank, but when you check the box it writes "TRUE" in that column, if you uncheck it, it writes "FALSE" - in other words, it will no longer be blank.

Sometimes a user will accidentally hit the check box on an unused row and then uncheck it, thus giving that row a "value", maybe that is confusing the macro?

Additionally (if it is helpful), you can use my last attachment and try it - if you click on a checkbox in one of the sheets and then uncheck it, running the summary button will recreate the problem from the image. I have been able to solve it by selecting all the non-used rows on the TNA sheets and "clear contents".

CME,
I really do not know about check box and the other issues you have with your data, I can only help you with the consolidation issue. We now need to close this thread for good. If you have further issues, you need to start as a new thread, otherwise, we will go for ever.