Help! Macro to consolidate data from multiple workbook sheets!

**Sainath Krishnan** · 05-07-2012, 05:25 AM

Hello Everyone!

I'm having a few problems with a macro that I use to consolidate data from multiple sheets. The macro works extremely well, but the problem is that a majority of the cells are formula based, not a direct value. So it ends up copying the cell formula rather than the actual value.

I looked through this forum for help and found several similar topics that suggest using paste special or use the .value property. However, I am confused about how to integrate that with my code! So I'm hoping that I can find help over here!

What the macro does/should do :
Copy the 12th Row of each sheet, from A12 to CI12, paste into the main excel.

The relevant part of the macro is :

Please Login or Register  to view this content.

Any help is greatly appreciated!

Sincerely,
Sai

**JBeaucaire** · 05-07-2012, 10:36 AM

This line of code is the problem:

Please Login or Register  to view this content.

VBA 101 - when you do a COPY/PASTE in a single line of code, the entire cell is copied as is.

Solutions:

1) Don't do copy/paste, do a .Value transfer

Please Login or Register  to view this content.

2) Use two lines of code, one to copy, the other to pastevalues

Please Login or Register  to view this content.

**Sainath Krishnan** · 05-07-2012, 10:43 PM

Yeah I figured that was the problem, but I had no idea how to integrate that into my existing code! Thank you so much for the quick reply! It worked like a charm.