VBA Runtime '9' Error when Doing file Save as

[kanddo2]

I have a 3 Macros in a workbook that are pulling tabs, pasting the information as values, auto populating the file name, and saving the file to same file path.

All three are identical, except for the tabs that are pulled from the file.

I continue to get a runtime '9' error when it gets to the input function I have set up. The debugger takes me to the script that triggers the save file name. The only difference in the code I have in this one that is different from the other two, is the cell reference that the name of the file is being pulled from. Code below.

Can anyone see why this code would result in this error? I am at a head scratching moment here, since I have identical Macros that do almost exactly the same thing, and function without error.

         '       Input box to name new file        
        NewName = InputBox("Name of your New Price Packet Schedule " & vbCr & _
        "Recommended Format:" & vbCr & _
        " " & vbCr & _
        "YYYY-MM-DD_Client Name - Pursuit Type_ID_Price File_vX.XX" & vbCr & _
        " " & vbCr & _
        "EX: 2015-05-25_ZYX Inc - ITO_4321234_Price File_v1.23", "New Copy", Worksheets("Opportunity").Range("I185").Value)
         
         '       Save it with the NewName and in the same directory as original
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
         
        .ScreenUpdating = True
    End With
    Exit Sub

 

[StephenCrump]

Run-time error '9': Subscript out of range?

That would suggest that the sheet "Opportunity" doesn't exist. You haven't provided your full code, so I am only guessing that:

The sheet "Opportunity" sits in ThisWorkbook, i.e. the workbook running the code, rather than in ActiveWorkbook, which is a new workbook you have created and want to save as .xlsx?

 

[kanddo2]

One of those DUH moments. You are correct and that now makes 100% sense. The sheet does not get transferred out, so it does not exist in the new file like it does in the other two Macros operations. I just need to change the reference to one of the sheets being pulled out.

Thanks for the push in the rgiht direction.