Method 'Range' of object '_Global'' failed - Query

[SimonGeoghegan]

Hi All,

I'm hoping someone may be able to point me in the right direction with this.

I have some code which filters data and subsequently creates individual spreadsheets using the filtered data. I am using a range to help inform my file name when I save the newly created spreadsheet, but I receive the following error when I run it.

Having done some reading, this error suggests that the object is incorrect/hasn't been referenced.

I have used

Dim Quarter As Variant

and then further down in the code, where I save the file, I have:

      ActiveWorkbook.SaveAs filename:= _
      xpathname & "Insurer Report - " & Range("Insurer") & " - " & Range("Quarter") & ".xls", FileFormat:=xlNormal, _
      password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
      CreateBackup:=False

The value for "Quarter" is identified using an InputBox where the entered value is used as a value for a particular cell. As that is the case, and works fine - I am assuming I have referenced this correctly, but then I become unstuck with the file saving.

Full code is here, can anyone see anything obvious that I am doing wrong? Apologies - the code itself is quite rudimentary - I have cobbled it together using bits of code I had lying around within other macros.

Sub Insurer_QuarterlyReporting()
Dim DateGenerated As Variant
Dim Insurer As Variant
Dim sht As Worksheet
Dim Quarter As Variant
Dim I As Integer, K As Integer, J As Integer
Dim iCount As Integer
Dim LaCell As String
Dim myDynArray As Integer
Dim Hosp() As Integer ' declares a static array variable
Dim ACells() As String
Dim iLastrow As Integer
Dim LastRow As Integer
Dim eValue As String
Dim dtimeStamp As String
Dim xpathname As String
Dim strtext As String
Dim C As String
Dim WS As Worksheet
Dim fso
Dim WS_Count As Integer
Dim FldrPicker As FileDialog

'ADD CODE IN FOR PUSHING DATA FROM SNAPSHOT INTO INSURER REPORT TEMPLATE
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
      .title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        MyPath = .SelectedItems(1) & "\"
    End With
    
    'In Case of Cancel
NextCode:
  MyPath = MyPath
  If MyPath = "" Then GoTo ResetSettings
  
  'Remove Breaks (Incidents)
  
  Sheets("Incident Narrative").Select
  Columns("E:E").Select
  
      Selection.Replace What:="<br>", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=2
    Selection.EntireColumn.AutoFit
    Cells.Select
    Range("A1").Activate
    Cells.EntireRow.AutoFit
    Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    
    'Remove Breaks (Complaints)
    Sheets("Complaint Narrative").Select
  Columns("J:J").Select
  
      Selection.Replace What:="<br>", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll ToRight:=2
    Selection.EntireColumn.AutoFit
    Cells.Select
    Range("A1").Activate
    Cells.EntireRow.AutoFit
    Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
  
  
    'Add 'Date Generated' Date
    
    DateGenerated = InputBox("Please enter the date that RiskMan data was ran (or will be ran from)")
    Sheets("Insurer Measures").Select
    Range("E1").Select
    Selection.Value = DateGenerated
    
    'Determine Quarter
    
    Quarter = InputBox("Please enter the Quarter (as 'Q1'etc.)")
    Sheets("Insurer Measures").Select
    Range("E2").Select
    Selection.Value = Quarter
    
  
    'Load Insurer Names
    I = 1
    Sheets("Brain").Visible = True
    Sheets("Brain").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    LaCell = Replace(ActiveCell.Address, "$", "")
    LaCell = Replace(LaCell, "A", "")
    myDynArray = CInt(LaCell)
    ReDim Hosp(1 To myDynArray)
    ReDim ACells(1 To myDynArray)

   'Load data into Array at runtime
    For iCount = LBound(Hosp) To UBound(Hosp)
        ACells(iCount) = Cells(I, 1).Value 'Name Code
        I = I + 1
    Next
      
    
    For I = 1 To UBound(Hosp)
    
    Sheets("Brain").Visible = False
    'Filter Incident Narrative Data by Insurer
    
        Sheets("Incident Narrative").Select
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Sheets("Incident Narrative").Select
        Sheets("Incident Narrative").Range("$A$1").AutoFilter Field:=13, Criteria1:=ACells(I)
        Range("A1").Select
        
         Selection.End(xlDown).Select
        C = ActiveCell.Address
        C = Replace(C, "$", "")
        C = Replace(C, "A", "")
        If CDbl(C) >= 65536 Then
            Range("A1:Y1").Select
        Else
        Range("A1:Y" & C).Select
        End If
       
        Selection.Copy
        Sheets("Incident Narrative ").Select
        Range("A1").PasteSpecial
        Columns("A:X").AutoFit
        Range("F1").CurrentRegion.Sort Key1:=Range("F1"), _
                                          DataOption1:=xlSortTextAsNumbers, _
                                          Header:=xlYes
        Range("A1").Select
        
        
    'Paste Incident Measures Tab
    
    Sheets("Insurer Measures").Select
    Range("B1").Value = ACells(I)
    Cells.Select
    Selection.Copy
    Sheets("Insurer Measures ").Select
    Cells(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Insurer Measures").Select
    Cells.Select
    Application.CutCopyMode = False
    Sheets("Insurer Measures").Select
    Selection.Copy
    Sheets("Insurer Measures ").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.Zoom = 80
    Cells.Select
    Selection.ColumnWidth = 28
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    
    'Filter Complaint Data by Insurer
    
        'Filter Complaint Narrative Data by Insurer
        Sheets("Complaint Narrative").Select
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Sheets("Complaint Narrative").Select
        Sheets("Complaint Narrative").Range("$A$1").AutoFilter Field:=13, Criteria1:=ACells(I)
        Range("A1").Select
         Selection.End(xlDown).Select
        C = ActiveCell.Address
        C = Replace(C, "$", "")
        C = Replace(C, "A", "")
        If CDbl(C) >= 65536 Then
            Range("A1:T1").Select
        Else
        Range("A1:T" & C).Select
        End If
        Selection.Copy
        Sheets("Complaint Narrative ").Select
        Range("A1").PasteSpecial
        Columns("A:T").AutoFit
        Range("E1").CurrentRegion.Sort Key1:=Range("E1"), _
                                          DataOption1:=xlSortTextAsNumbers, _
                                          Header:=xlYes
        Range("A1").Select
        
    'Start of Save the temp Sheets into folders
      
     Sheets(Array("Insurer Measures ", "Incident Narrative ", "Complaint Narrative ")).Copy
     Sheets(Array("Insurer Measures ", "Incident Narrative ", "Complaint Narrative ")).Select
                  
       Quarter = Range("Insurer Measures").Cells(2, 5)
                  
      Application.DisplayAlerts = False
      ActiveWorkbook.SaveAs filename:= _
      xpathname & "Insurer Report - " & Range("Insurer") & " - " & Range("Quarter") & ".xls", FileFormat:=xlNormal, _
      password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
      CreateBackup:=False
           
        'ActiveWorkbook.Save
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
        
    For Each WS In Sheets(Array("Insurer Measures ", "Incident Narrative ", "Complaint Narrative "))
        WS.Select
        Cells.ClearContents
        Cells.UnMerge
        Cells.Interior.Color = RGB(255, 255, 255)
        Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
        
        Next WS
    Next
    
    
    MsgBox ("Insurer Reports are now complete.  Send to Quality Improvement Team.")
    
    
ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.ScreenUpdating = True
         
End Sub

Thanks to all in advance!

Simon

 

[Jeffrey Mahoney]

My first thoughts. The input box returns a range, not a name of a range.
Range("Quarter")
should be
Quarter.value

I'm kinda picky with my code. I believe it is better to get the value from the range rather than referencing just the range, as in:
Range("Insurer")
should be
Range("Insurer").value

Please try

 

[SimonGeoghegan]

Thanks for the reply Jeffrey.

In terms of the suggestions you've made, should these be made in the portion of code around the file saving? I've tried that but it gave me a slightly different error around an object being required, as opposed to the initial "Global" error.

Regards,
Simon

 

[RoryA]

Assuming your Quarter variable should contain the name of a range, the code should be:

Range(Quarter)

rather than:

Range("Quarter")

.

PS You're not the Simon Geoghegan (winger for Ireland) are you?

 

[SimonGeoghegan]

Unfortunately I'm not - coincidental I'm afraid!

Thanks for the reply. So in the input box (for Quarter), I am entering "Q4" and I subsequently want to use that "Q4" within the filename. I've tried changing my code to how you've mentioned but I'm still getting the same issue I'm afraid.

 

[RoryA]

Shame - would have made my day!

Unless you mean to refer to cell Q4, you should just be using Quarter and not Range(Quarter).

 

[SimonGeoghegan]

That's done the trick - thanks Rory & Jeffrey!

 

[Fluff]

PS You're not the Simon Geoghegan (winger for Ireland) are you?

and, more importantly, Bath

Unfortunately I'm not - coincidental I'm afraid!

Shame

 

[RoryA]

and, more importantly

I think we'll agree to disagree there!

 

[Fluff]

OK