Copying rows from multiple documents into single sheet

**Kovu159** · 03-28-2012, 09:46 PM

Hello,

For a project at work, I have a folder full of identicle documents. On the page "Manager Summary" is a 5 row table I need to copy into a consoidated list. That consolidated list needs each table from each document in a long table. Essentally it is copying every table and pasting them one underneith another in a consolidated spreadsheet.

In my consolidation form, I have the following code:

Please Login or Register  to view this content.

This is doing a couple wierd things. It opens the first file, selects the rows, then some wierd things happen. First,
Error.png
Attachment 147820

I get this error: Method "PasteSpecial" of object '_Worksheet' failed

Also, it opens up a new workbook with a copy of the sheet "Manager Summary".

Google has failed me, and I have banged on this code for awhile, any help would be greatly appreciated!

**PingPing** · 03-29-2012, 04:53 AM

You've got a lot of Activate/Select/Copy/Paste stuff in your code that is not necessary and is difficult to follow.
I'd suggest trying the following code and see if that gives you errors.
Note: I've assumed the worksheet you're 'pasting' the data to is called "Sheet1". If not, you'll need to change the line of code accordingly.

Please Login or Register  to view this content.

**Kovu159** · 03-29-2012, 10:38 AM

You are my hero PingPing! Thanks!

I'm marking this as solved, but have one other quick question. The rows I am copying have merged cells and conditional formatting that highlights certain cells based on their values. Is there a way to use a PasteSpecial to carry over these merged cells and formatting?

Thanks!