Repeated action - shorten macro - copy from each sheet to summary sheet

Hello,

I have this macro which I have generated and edited with the recorder.

Please Login or Register  to view this content.

This macro copies a range from the first sheet of the workbook and pastes it in the sheet named "Summary" in C4
then copies the same range from the second sheet of the workbook and pastes it in the sheet named "Summary" in C7
and so on...I would like to begin the copy/paste action, starting from the first worksheet and continuing in the order the worksheets are in the workbook. This is the reason I edited the above macro as above.

Is it possible to shorten the macro and to do the above for all the sheets found in a workbook, starting from sheet1 and continuing to the last sheet of the workbook, but disregard sheets named, Summary2 and summary3?

Thank you

Hi arlu201,

thanks for your reply. Yes all the data from the other sheets should be copied into the summary sheet. starting from c4, then c7,then c10, then c13 and so on.

So

range W1785:ag1786 in worksheet(1) should be copied in summary sheet in C4
range W1785:ag1786 in worksheet(2) should be copied in summary sheet in C7
range W1785:ag1786 in worksheet(3) should be copied in summary sheet in C10
range W1785:ag1786 in worksheet(4) should be copied in summary sheet in C13
range W1785:ag1786 in worksheet(5) should be copied in summary sheet in C16

and so on.

Thank you

I found the macro by RDB, CopyRangeFromMultiWorksheets here

http://www.rondebruin.nl/copy2.htm

Please Login or Register  to view this content.

I have manage to edit it to achieve what I was looking above.

I am encountering a small issue though. When I am trying to consolidate a second range, AU1785:BE1786 to the Summary worksheet, the consolidated range AU1785:BE1786 in the summary worksheet is pasted below the consolidated W1785:ag1786. Is it possible to have them pasted at the same starting row? I can provide an example, if the above is not making much sense.

Thank you

Hi arlu1201,

The macro works great! One small issue, the values that are copied contain formulas, so when they are copied to the summary sheet they return a #REF error.
Is it possible to copy only the values and source formatting?
Thank you for your help!

To my surprise I managed to edit the code successfully,

Please Login or Register  to view this content.

Thank you for your help and the simple yet effective macro!

When I try to paste a different range in column O

Please Login or Register  to view this content.

for some reason the paste special does not work, the values are copied in a different font/formatting. Trying to figure this out and I'm sure its something obvious but don't know what.

Thank you

That did it, everything works great! Many many thanks for the help Arlette! Thread solved