PDA

View Full Version : Summary worksheet



riles21
01-19-2007, 11:44 PM
I have workbooks that all have the same exact layout. Is there anyway to run a macro that will:

1) Prompt user to select workbooks within a folder (not a specific one - I believe this is the GetOpenFilename command)
2) Copy a range of values (for example A4 and C5:C28) from each worksheet within each workbook.
3) Paste the copied range (A4 from first workbook would go in B1 with the range C5:C28 from first workbook would go in B2:B25). Information from second worksheet/workbook would go in C1 AND C2:C25. Information from third worksheet/workbook would go in D1 AND D2:D25, etc etc until there are no more workbooks or worksheets.

Essentially this is a summary worksheet...

Simon Lloyd
01-20-2007, 02:24 AM
Hi, there are limitations as Excel 2003 only has 256 columns so if you have more data than that you will not be able to achieve the format you require!

As for the code to do what you want http://vbaexpress.com/kb/getarticle.php?kb_id=829 would be a good place to start, you should be able to add your own modifications to malik641's code.

Regards,
Simon

riles21
01-20-2007, 09:39 AM
Thanks for the help - one other thing...

I get an error because some of the worksheets are protected. Is there a way to bring the info in and leave the sheets protected?

Thanks!!!!!!

Bob Phillips
01-20-2007, 10:20 AM
You need to unprotect it, copy the data in, then protect it again.

RichardSchollar
01-20-2007, 11:32 AM
You need to unprotect it, copy the data in, then protect it again.

Although if you don't plan on saving the source workbook, you can skip the reprotect step.

:hi: Bob, I have half a bottle of Sapphire that's looking definitely short-lived tonight!

Bob Phillips
01-20-2007, 11:48 AM
Too much work, nothing else to do :)

Just send my portion over here, I've got the lemon, got a chilled glass, and I'm ready.