Macro involving multiple sheet and files

[dguenther]

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:

Sub trialuptojudge()
'
' trialuptojudge Macro
'

'
Sheets.Add
Sheets("VT_State_Cases,_Combined2011-06").Select
Sheets("VT_State_Cases,_Combined2011-06").Move Before:=Sheets(1)
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
Range("C1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
End Sub
Sub findjudge()
'
' findjudge Macro
'

'
Cells.Find(What:="judge", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("A43:A44").Select
Selection.Cut
Sheets("Sheet1").Select
Range("H1").Select
ActiveSheet.Paste
End Sub
Sub concatenate()
'
' concatenate Macro
'

'
Range("E1").Select
Columns("D:D").EntireColumn.AutoFit
Range("F1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],R[1]C[2])"
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F1:N2").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

****
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

 

[dguenther]

{Edit 6/15/2011}

Here is the code more nicely shown:

Sub trialuptojudge()
'
' trialuptojudge Macro
'

'
Sheets.Add
Sheets("VT_State_Cases,_Combined2011-06").Select
Sheets("VT_State_Cases,_Combined2011-06").Move Before:=Sheets(1)
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
Range("C1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.Cut
Sheets("Sheet1").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("VT_State_Cases,_Combined2011-06").Select
End Sub
Sub findjudge()
'
' findjudge Macro
'

'
Cells.Find(What:="judge", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("A43:A44").Select
Selection.Cut
Sheets("Sheet1").Select
Range("H1").Select
ActiveSheet.Paste
End Sub
Sub concatenate()
'
' concatenate Macro
'

'
Range("E1").Select
Columns("D:D").EntireColumn.AutoFit
Range("F1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],R[1]C[2])"
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F1:N2").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub