Cycling through files in a folder

Hi all,

I'm a novice with VB, so go easy on me. I'm trying to write a macro to copy the same couple of cells from hundreds of excel files and paste them into one summary file. What I've written so far is:

Please Login or Register  to view this content.

Forgive the clumsy break down of the files; they range from 001 to 063, and since I don't know how to set x as a three digit number I decided to have a section for 1-9 and a second section (with the same code) for 10-63.

The problem I think I'm having is in referencing the workbook (the debugger always stops on the worksheets(wb).Activate line). Ideally I'd like the macro to open the files itself, but given that I can't even make it work when they're open I think I should start small. The issue, I believe, is that I'm not referencing the file correctly, since I'm trying to use the 'name + x' format to cycle through the files, and either I'm doing it wrong or this sort of variable referencing isn't suitable for the function. I've tried all manner of different tactics to get the activate workbook function liking my workbook name, but have had no luck.

Any help would be hugely appreciated, and once again, forgive my unsubtle coding.

Kate

Hi,

I think your error may be here

Please Login or Register  to view this content.

this will set wb to namex.xls

try

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

There's a couple of places that you have variables in quotes, which will result in the code not working.

There's a ready made macro here which you may find useful

Try activating a worksheet in wbNew by replacing this:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

I've used sheet 1 for an example.

This may help (or not); tryu replacing this code

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Hope this helps.
J

hi Kate,

This may need some fine tuning but hopefully it will do what is needed once the changes are made - my idea of not using copy & paste removes the need to flick between files, removes the risk of something else being mistakenly put on the clipboard or the clipboard being cleared while the macro is running (it may be faster too but I'm not sure). I've included the LastCell Function, rather than coding it into the same macro, as you may find it useful for other code development too.

Kate/Dave, some of your code in posts 5 & 6 respectively seems to be missing the dot prefixes for the range or sheet within the With statements.

- fingers crossed...

Please Login or Register  to view this content.

hth
Rob

Thanks for the feedback & marking the thread as solved - I'm pleased we could help :-)

Rob