Can anyone make sense of this VBA Code?

[leeb91]

Basically, I have four data files all of which are named (1),(2),(3),(4) respectively.
I have this VBA code on my template sheet that only has column headings that I need for column heading standardization.
I would like to open each of the data files and change its column headings that correspond to the ones in the template files (As shown in the vba code).
Then, within the file (1), one by one I would like the column heading to the ones in the template to check if they are matching. If they are, then I would copy the entire column under the matching column headings and paste into the template file. Then, it has to be saved as a different name and this will be the final product that I want.
To proceed to file(2), the template file will have to be erased except for the column headings for matching. However, the code doesn't seem to work. Any experts out there that can help me with this?
Thanks a lot!


Here is the code:

Sub Test()

Dim dbook As Workbook
Dim rngCri As Range
Dim rngTemp As Range
Dim N As Long
Dim wb As Workbook

N = 1
Do
tfile = "C:\" & "(" & N & ")" & ".xlsx"
tfile1 = "C:\" & "New" & "(" & N & ")" & ".xlsm"
Set rngCri = ThisWorkbook.Sheets(1).Range("a1")
On Error GoTo K:
Set dbook = Workbooks.Open(Filename:=tfile)
Set rngTemp = Range("a1")
Do
'Claim_Count
If Left(rngTemp, 9) Like "Claimants" Then
rngTemp = "Claim_Count"
'Claim_ID
ElseIf Left(rngTemp, 2) Like "ID" Or Left(rngTemp, 11) Like "Client ID #" Or Left(rngTemp, 8) Like "Claim ID" Or Left(rngTemp, 6) Like "CaseID" Then
rngTemp = "Claim_ID"
'Claim_Status
ElseIf Left(rngTemp, 13) Like "Matter Status" Or Left(rngTemp, 10) Like "Resolution" Or Left(rngTemp, 12) Like "Dismissal or" Then
rngTemp = "Claim_Status"
'Claimant_Deceased
ElseIf Left(rngTemp, 8) Like "Deceased" Then
rngTemp = "Claimant_Deceased"
'Claimant_Diagnosis_Date
ElseIf Left(rngTemp, 10) Like "Diagnsis D" Then
rngTemp = "Claimant_Diagnosis_Date"
'Claimant_DOB
ElseIf Left(rngTemp, 3) Like "DOB" Then
rngTemp = "Claimant_DOB"
'Claimant_DOD
ElseIf Left(rngTemp, 3) Like "DOD" Then
rngTemp = "Claimant_DOD"
'Claimant_Employee
ElseIf Left(rngTemp, 14) Like "Employee Claim" Then
rngTemp = "Claimant_Employee"
'Claimant_Name
ElseIf Left(rngTemp, 8) Like "Claimant" Or Left(rngTemp, 8) Like "LastName" Or Left(rngTemp, 18) Like "Claimant Last Name" Then 'Exact Matching Needed for Claimant
rngTemp = "Claimant_Name"
'Company/Entity/PC
ElseIf Left(rngTemp, 7) Like "Company" Or Left(rngTemp, 8) Like "Employer" Then 'Exact Matching Needed
rngTemp = "Company/Entity/PC"
'Coverage
ElseIf Left(rngTemp, 9) Like "Case Cate" Or Left(rngTemp, 13) Like "Coverage_Code" Then
rngTemp = "Coverage"
'Disease_Category
ElseIf Left(rngTemp, 7) Like "Disease" Or Left(rngTemp, 11) Like "DiseaseName" Then
rngTemp = "Disease_Category"
'Expense_Amount
ElseIf Left(rngTemp, 13) Like "Defense Costs" Or Left(rngTemp, 11) Like "Defense Fee" Then 'What if there are 2 columns "Defense Cost" and "Defense Fee" -> Combine?
rngTemp = "Expense_Amount"
'File_Date
ElseIf Left(rngTemp, 10) Like "Date_Filed" Or Left(rngTemp, 9) Like "DateFiled" Or Left(rngTemp, 15) Like "Suit Filed Date" Then
rngTemp = "File_Date"
'First_Exposure_Date
ElseIf Left(rngTemp, 4) Like "DoFE" Or Left(rngTemp, 4) Like "DOFE" Or Left(rngTemp, 19) Like "First Exposure Date" Then
rngTemp = "First_Exposure_Date"
'Indemnity_Paid
ElseIf Left(rngTemp, 15) Like "Indemnity Costs" Or Left(rngTemp, 15) Like "Settlement_Paid" Or Left(rngTemp, 9) Like "Indemnity" Or Left(rngTemp, 16) Like "Total Settlement" Then
rngTemp = "Indemnity_Paid"
'Jurisdiction/County
ElseIf Left(rngTemp, 6) Like "County" Or Left(rngTemp, 12) Like "Jurisdiction" Or Left(rngTemp, 13) Like " Jurisdiction" Then
rngTemp = "Jurisdiction/County"
'Last_Exposure_Date
ElseIf Left(rngTemp, 4) Like "DOLE" Or Left(rngTemp, 18) Like "Last Exposure Date" Then
rngTemp = "Last_Exposure_Date"
'Local_Defendent_Firm
ElseIf Left(rngTemp, 15) Like "Defense Counsel" Then
rngTemp = "Local_Defendent_Firm"
'Plaintiff_Law_Firm
ElseIf Left(rngTemp, 15) Like "Adversary Firms" Or Left(rngTemp, 17) Like "Plaintiff Counsel" Or Left(rngTemp, 19) Like "PltfAtty" Or Left(rngTemp, 14) Like "Plaintiff Firm" Then
rngTemp = "Plantiff_Law_Firm"
'Resolution_Date
ElseIf Left(rngTemp, 13) Like "Date Resolved" Or Left(rngTemp, 15) Like "Resolution Date" Or Left(rngTemp, 14) Like "Date Dismissed" Then
rngTemp = "Resolution_Date"
'State_Filed
ElseIf Left(rngTemp, 5) Like "State" Then
rngTemp = "State_Filed"


End If
Set rngTemp = rngTemp.Offset(0, 1)
If rngTemp = "" Then Exit Do
Loop

Do
dbook.Activate
Set rngTemp = Range(Range("a1"), Range("a1").End(xlToRight))
Set rngF = rngTemp.Find(what:=rngCri, lookat:=xlWhole)

If rngF Is Nothing Then
Else
If rngF.Offset(1, 0) = "" Then
Else
Range(rngF.Offset(1, 0), rngF.Offset(1, 0).End(xlDown)).Copy
ThisWorkbook.Activate
rngCri.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End If
Set rngCri = rngCri.Offset(0, 1)

If rngCri = "" Then Exit Do 'Exit template file if cell is empty

Loop
Application.DisplayAlerts = False
dbook.Close True 'Save standardized columns
ThisWorkbook.Save 'Save current workbook
ActiveWorkbook.SaveAs Filename:=tfile1

N = N + 1
If N = 5 Then Exit Do 'Meaning there are 4 Data Files; Exit on the 5th file
Loop
K:
End Sub