Macro involving multiple sheet and files

Hello,

I am new to this forum, but I have familiarity with Excel, and some familiarity with VBA and macros.

I'll try to explain my project succinctly.

(1) I am going to have a lot of .xlsm files that were recently converted from .txt

(2) Each of these .xlsm files will have a good deal of data in them, some extraneous, some important. It is all actually legal text. I want to cut out all junk and just keep the good stuff.

(3) I'm doing my best, but I need some help!

Here's what a typical file looks like:

1 of 26 DOCUMENTS

In re Phillips

Arizona Supreme Court No. SB-10-0036-D

SUPREME COURT OF ARIZONA

244 P.3d 549; 2010 Ariz. LEXIS 52; 597 Ariz. Adv. Rep. 19


December 16, 2010, Decided



DISPOSITION:*Disciplinary Action from the Disciplinary Commission SUSPENSION AND
PROBATION ORDERED.

CASE SUMMARY:


PROCEDURAL POSTURE: Respondent attorney argued that the Hearing Officer
erroneously used a vicarious.....

OVERVIEW: The attorney argued that the Hearing Officer used an improper standard
of vicarious liability in finding violations of Ariz. R. Prof. Conduct 5.1(a)
and 5.3(a) .........

OUTCOME: The recommended length of the attorney's suspension was modified to six
months, and all other recommendations were accepted.

JUDGES:

***... [**1] John Pelander, Justice. CONCURRING: Rebecca White Berch, Chief
Justice, Andrew D. Hurwitz, Vice Chief Justice, Jon W. Thompson, Judge.
WEISBERG, Judge, concurring in part and dissenting in part....



I want it to look like:
Herring v. Herring + No. 10-017 + 2011 VT 38; 2011 Vt. LEXIS 48 +May 5, 2011, Filed +Present: Reiber, C.J., Dooley, Johnson, Skoglund and Burgess, JJ.REIBER, C.J., dissenting....
with + signifying a new cell, but I want them all to be on the same row.

****
Now, I have written a series of macros that made the transformations I wanted. But, it only works on that exact file and group of text, not on another similar but distinct file.

Here are the macros:

Please Login or Register  to view this content.

****
So, it seems like the first major problem I am encountering is my second step where I select sheet "VT_State_Cases,_Combined2011-06". The next time I try to run it on a different file, it's not called that obviously, and Excel yells at me with a run-time error 9 subscript out of range

Any help on that first step?

Thanks so much and tell me anything more that I could post that would help you help me!

Sincerely,
dguenther

Attached is a sample file I would want to remove the unnecessary text from