0

'-2147319767 (80028029)':

Been using this code for over a year now. Suddenly today, it gets the above run-time error when calling out certain sheet names or calling out Activesheet.

Absolutely no idea why it decided not to function today.

'''

Sheets("WIP Shortage").Select     
Range("A:CB").Select
Selection.Delete Shift:=xlUp
Range("CC1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("CC1").Select
Selection.NumberFormat = "yyyy m-d;@"
ChDir "S:\Skim Kits\WIP Shortage Report"
 Workbooks.Open Filename:= _
"S:\Skim Kits\WIP Shortage Report\" & Range("CC1").Text & " GEUD_WIP_Job_Shortage_Shop_Fl_ELO.xlsx"
Range("CC1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("CC1").Select
Selection.NumberFormat = "yyyy m-d;@"
Range("CC2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("CC2").Select
Selection.NumberFormat = "yyyy-m-d;@"
Cells.Select
Selection.Copy
Windows("Availability-Shortages" & Range("CC2").Text).Activate
Cells.Select
ActiveSheet.Paste   
Application.CutCopyMode = False
Range("CD2").Select
ActiveCell.FormulaR1C1 = "=ISOWEEKNUM(RC[-76])"
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Windows(Range("CC1").Text & " GEUD_WIP_Job_Shortage_Shop_Fl_ELO.xlsx").Activate
ActiveWindow.Close


' Paste Thiswk Lastwk formula as values on QMI Targets
Application.Calculation = xlManual
Sheets("WIP Shortage").Select  
Range("CE2").Select
ActiveCell.Formula = "=CD2+1"
Application.Calculation = xlAutomatic
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Application.Calculation = xlManual
Sheets("QMI TARGETS").Select   
Range("AL2").Select
ActiveCell.Formula = "=SUMIFS('WIP Shortage'!L:L,'WIP Shortage'!K:K,A2,'WIP Shortage'!E:E,""OP"",'WIP Shortage'!CD:CD,ISOWEEKNUM(NOW()))"
Selection.Copy
Range("AL2:AL300").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.Calculation = xlAutomatic
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Application.Calculation = xlManual
Range("AM2").Select
ActiveCell.Formula = "=SUMIFS('WIP Shortage'!L:L,'WIP Shortage'!K:K,A2,'WIP Shortage'!E:E,""OP"",'WIP Shortage'!CE:CE,ISOWEEKNUM(NOW()))"
Selection.Copy
Range("AM2:AM300").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.Calculation = xlAutomatic
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

'''

This is an excerpt from the entire code, but that first line is the first of 4 places it faults out. If I debug and manual select the sheet and move down the to the next line and run, it goes fine until I try to call out active sheet on line 23.

This is the code that precedes it and it runs fine. You'll notice it calls out my "today" sheet just fine and even renames it.

'''

 Sheets("Today").Select
If Range("C5") = "Fri" Then

    Sheets("Fri").Select
    If Range("C5") = "Fri" Then
    Sheets("Fri").Select
    ActiveWindow.SelectedSheets.Delete
    End If
End If

'''

List of sheet names in the workbook

gomugomuking
  • 47
  • 7
  • 5
    This might be a good time to implement the suggestions in [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen May 12 '20 at 12:38
  • Definitely will look into this. Tried running this with a Dim MyWksht set and it faulted out, but I will play around with it. Really puzzling though on why it suddenly does not compute. – gomugomuking May 12 '20 at 12:54
  • Is it possible a different Workbook is in scope, and that workbook doesn't contain the named sheet? Maybe try referencing it with the fully qualified name and see if that helps? For example: `Workbooks("Book1").Sheets("WIP Shortage")....` – basodre May 12 '20 at 13:05
  • @user3561813 Just gave it a try to rule it out. To no avail. – gomugomuking May 12 '20 at 13:16
  • updating post with preceding code. – gomugomuking May 12 '20 at 13:19
  • What's more is if I change "WIP Shortage" to "Today" it works fine and will find that worksheet. Just not the worksheet I need it to find. – gomugomuking May 12 '20 at 13:45

0 Answers0