Macro to Copy Cells From One Workbook into Another as Text

'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK
Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.

Workbooks to Sheets - Part 4

The parts of the code that need to be edited are colored to draw your attention.

================
To copy from row11 and then paste as values, you'd need to change this one line of code:

Please Login or Register  to view this content.

to these lines of code, this also changes the original workbook to values only:

Please Login or Register  to view this content.

To save the original file with other filenames would be a change here:

Please Login or Register  to view this content.

to this, perhaps:

Please Login or Register  to view this content.

I think this is a patently bad idea, by they way. You're in the process of cycling through files in a folder and then you start adding files to that folder.... I bet this macro would run out of control.

I'd recommend you have a folder INSIDE this folder called "Values" and you save these new copies into that folder instead.

Please Login or Register  to view this content.

No, the macro is grabbing files "one at a time" from the folder you designate. If the number of files in that SAME folder were to get bigger and bigger as the macro proceeds, it would never end. It would eventually start finding the files you had just added and open them and do the same thing... kind of crazy.

I suppose you could test each filename before you open it to make sure it doesn't have "1.xlsx" in the filename....

But I'd still save new files to a different folder to not mix them.

Hey Jerry,

I followed the directions for #4 in the linked site and made the edits recommended above. In the process I went with your suggestion and created a "values" folder so the macro would not run endlessly. I then created a master file in the same folder as the data files, opened that one, created 4 tabs, named them as they're named in the individual files, and ran the macro (which is saved in a different file that contains all of my macros) and it returned an error that reads "Run-time error '9': Subscript out of range."

Here is the code, with the problem (as identified by the debugger) in yellow font:

Please Login or Register  to view this content.

Any ideas??


Thanks again for your help.

"Run-time error '9': Subscript out of range."

This means an named object cannot be found by the exact name specified. This is often due to simple misspellings, but other times to hidden spaces and such that make tab names LOOK the same but are not when examined very closely. You will need to identify the exact error in sheet tab names, because this is exactly what that error means.

My bad, this will probably need to be fixed, too:

Please Login or Register  to view this content.

[/CODE]

send me your master file and a source file giving the error, I'll test it out.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbooks.

One would expect you replace sensitive data with junk data, whether sent privately or posted to the forum. To test this particular problem you could actually replace the data with absolute garbage since it's irrelevant. We're just trying to examine your sheet names and how the macro is coercing them.

Um, wouldn't your edited macro be in the Master? I don't see it installed there.

Anyway, I took the two files above and the macro from further up and it worked bingo bango. Need to update this, though, never expect people to skip the top rows/columns, but some peeps, do...

Please Login or Register  to view this content.

Maybe. THe original macro uses a THISWORKBOOK reference, I expected the macro to be installed in the Master. That's why the sheet names aren't matching up, it's looking at your other workbook.

Be sure to make the edit I noted above.

Have you tried using the autofilter? You can filter a column of data using the autofilter to only shows rows with a value of .8 or less (is less right?) before you copy and paste.

Heavens, no, you'd have the macro autofilter for you before the copy command.

Assuming the column to filter is column 13 (column M) the first row of data is row11 (apply filter to row 10), something like this:

Please Login or Register  to view this content.

Hey Jerry,

The code you posted for this last part is fine, the problem is the column and row vary from sheet to sheet, which is completely my fault for not making them uniform. That being said I tried to work around it by writing that code into the part where I copy the "Cumulative Share Avg." value in each sheet (because in that part I go sheet by sheet), but it gave me the "Run-Time Error '91': Object Variable or With Block Variable Not Set" error. I've copied the code below with the portion the debugger highlights in yellow text. I think this is because at this point in the code WSMain has not been defined, I'm just not sure how to fix it.....

Please Login or Register  to view this content.

Maybe this:

Please Login or Register  to view this content.

The goal in final tweaking is to remove all "selecting" and "Activating" and send all your commands to the target sheets/workbooks using your variables.