Excel File Names Not Matching Contents of file

[EliseFreedman]

Hi There

I have set up an excel spreadsheet which imports data from our accident reporting system and then code filters the data into individual reports split by location. The individual worksheets have the location in the name. I thought this was working OK. However, I have now discovered that the file names are not matching the data in the file for example the file name is Scotstoun Module Hall M2 whilst the data in the file is Scotstoun ship 1063.

I think its something to do with the logic of the code but I cant see what I need to change. Can anyone help?

Sub FilterData_Location()
Application.ScreenUpdating = False

Worksheets("Statements Overdue Report").Select
'   Local Variables
    Dim blnProjIdMatch          As Boolean, blnCustIDMatch          As Boolean
    Dim rngProjStatus           As Range
    Dim strProjID               As String, strCustID                As String
    Dim strProjID2               As String
    Dim ReportDate As Variant
    Dim AreaName As String
'   Step 1 : Retrieve information
ReportDate = InputBox("Enter The End Date For the Report (dd.mm.yy)")
For Each r In Sheets("Menu").[J4:M38].Rows
Area = r.Cells(1)
Manager = r.Cells(2)
FirstName = r.Cells(3)
Email = r.Cells(4)
Range("c_SelProjID") = Area
Range("c_SelDate") = ReportDate
    strProjID = Range("c_SelProjID")
'   Step 2 : Clear any existing data
    Worksheets("Statements Overdue Report").Range("7:65536").ClearContents


    '   Step 4 : Get data from status page
    For Each rngProjStatus In Range("ps_ProjectNums")
        blnProjIdMatch = False

             If rngProjStatus = strProjID Or strProjID = "" Then blnProjIdMatch = True
               If blnProjIdMatch = True Then
            rngProjStatus.EntireRow.Copy Destination:=Worksheets("Statements Overdue Report").Range("A65536").End(xlUp).Offset(1, 0)
        End If
    Next rngProjStatus
Call RemoveValidation
  
    'Make a copy of the active sheet and save it to
     'a temporary file
      ActiveSheet.Copy
    Set WB = ActiveWorkbook
    
    FileName = "Overdue Accident Statements for Accidents in " & Area & " as at " & ReportDate & ".xls"
    Debug.Print FileName
    On Error Resume Next
    On Error GoTo 0
    WB.SaveAs FileName:="H:\SHE Documents\Overdue Accident Statements Clyde\" & FileName
    Next
End Sub

 

[SkipVought]

Elsie,

I'm not sure what question you are asking. You state,

I have now discovered that the file names are not matching the data in the file for example the file name is Scotstoun Module Hall M2 whilst the data in the file is Scotstoun ship 1063.

Personally, I try to avoid chopping data up into different locations (sheets). I'd rather use Excel data analysis and reporting tools and controls to handle subsets od data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Aera = r.cells(1).value