Code that extracts specific sheet in multiple workbooks to one workbook

Perhaps a macro like this would work for you?

You must change the sPath ("C:\Temp\") to suit your needs as a desktop folder contains user name as well. This macro loops through all files and copies the "used range" on sheet2 so if there is more in column M, N .... this will be copied too.

Macro also stips away headings.

Please Login or Register  to view this content.

Alf

Thank you! Where would I put this code? in a blank file?

I cant get this to work. Ive tried pasting the code in both an existing and a new workbook, each in the sheet, workbook, and module. Not sure if I am doing something wrong?

Did you change the "sPath" to the folder on the desktop where the files are found?

Don't forget the last "\" the code will not work if you forget it.

Adding the code to a workbook go to "Developers" click on "Visual Basic" icon. In the new windows that opends click on "Insert" and select "Module". Paste the code into the module.

Close the "Microsoft Visual Basic" window. You are now back in the "Developer" tab. Click on the "Macros" tab in the pop-up window you should now see a macro (ProcessAllFiles) click "Run" and this should start the import.

Alf

Sorry my bad!

Change the line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Alf

I did both and it still doesn't work. all of the sheets are protected, I forgot to add that. I tried wrapping

Please Login or Register  to view this content.

but it still did nothing

Ok so at the moment the password protection stops the macro from working. I'll do a bit of testing to see how to fix that.

What you could do in the meantime is to record a macro where you go through all the steps, selecting a file, activating the "Stp2" sheet, entering password, do the copying and close the file.

Could you post this macro, and if password shown in "clear" text replace that with *******.

Alf

Alf- first off thank you so much for your help, it is greatly appreciated.

Second, I went in and manually unprotected the step2 sheet in 4 example workbooks. I put the code in a blank workbook module and it worked! Everything pasted in how I needed it to. The only issue was that a box popped up saying:

"There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?"

The code will only work if I select yes. My only concern is that the test data is relatively small compared to all the data I will have.

Also, can we have it so it defaults to Yes and doesn't show the box?

"There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?"

the line below should thake care of this I thought, but it don't seems to do that.

Please Login or Register  to view this content.

Then we better change the line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

and finaly

Please Login or Register  to view this content.

And the syntax for opening a password protected file is as you already have solved yourself is

Please Login or Register  to view this content.

If this solves you problem don't forget to mark your thread "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Strange, worked ok on my PC but as long as your syntax work then it does matter. Hmm I'll have to do a bit of testing to see if I can find out why my suggested setting for removing password did not work.

At least I hope you got rid of the

There is a large amount of information on the Clipboard.

Alf

To things, yes the syntax is off (late evening in Gothenburg) proper syntax is

Please Login or Register  to view this content.

I'm not to happy with this part of the code as I feel it is a bit unstable

Please Login or Register  to view this content.

I think this is a better code

Please Login or Register  to view this content.

Alf

Both changes worked (the password and the better code). Thank you so so much for hour help Alf! You have no idea how much I appreciate it!

You are welcome and thanks for the rep!

Alf