Run-time Error '1004': Method 'Cells' of Object '_Global' failed

[c2shar]

I have excel based calculation model which interfaces with 6 other excel sheets. However, if i save all the files when i close the model, i get subject error for the following code. Not able to track what is the issue. Model usually jumps at code line starting wb2.activeworkbook..... OR at owb.ActiveWorkbook...... Please advise:




Private Sub Workbook_Open()


Application.DisplayFullScreen = True

startup.Show



If MsgBox("Do You Wish To Load All Models", vbQuestion + vbYesNo) = vbYes Then


If Workbooks.Count > 1 Then

MsgBox "PLEASE CLOSE ALL OTHER EXCEL FILE ALREADY OPEN BEFORE RUNNING THIS MODEL"

Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value = " "

For i = 1 To Workbooks.Count

Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value = Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value & Chr(10) & Workbooks.Item(i).Name

Next i


MsgBox "The List of Workbooks open are: " & Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value


Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value = " "

closemodel

GoTo Hop ' another function


End If




If Worksheets.Count > 13 Then

MsgBox "EXTRA WORKSHEETS DETECTED :KINDLY ENSURE THE FACILITY INPUT OR OUTPUT SHEETS ARE NOT DUPLICATED"

Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value = " "


For i = 1 To Worksheets.Count

Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value = Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value & Chr(10) & Worksheets.Item(i).Name

Next i


MsgBox "The List of sheets are: " & Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value


Cells(Cells.Rows.Count, Cells.Columns.Count - 1).Value = " "

closemodel

GoTo Hop


End If






Dim owb, wb2 As Workbook
Dim file, file2 As Variant, flag, index As Integer
Set owb = ActiveWorkbook




owb.Application.Sheets(1).Cells(Cells.Rows.Count, Cells.Columns.Count).Value = " "
owb.Application.Sheets(1).Cells(Cells.Rows.Count - 1, Cells.Columns.Count).Value = " "
owb.Application.Sheets(1).Cells(Cells.Rows.Count - 2, Cells.Columns.Count).Value = " "




ActiveWorkbook.Application.Sheets(1).Cells(Cells.Rows.Count, Cells.Columns.Count).Value = owb.Name
ActiveWorkbook.Application.Sheets(1).Cells(Cells.Rows.Count - 1, Cells.Columns.Count).Value = owb.Name


Sheets("MAP").Select


fpath = Left(ActiveWorkbook.FullName, InStr(ActiveWorkbook.FullName, ActiveWorkbook.Name) - 1)


For i = 1 To Range("C52").Value


flag = 0

fname = Cells(46 + i, 4)

file = Dir(fpath)

While (file <> "")
If InStr(LCase(file), LCase(fname)) > 0 Then

file2 = file
flag = 1

End If
file = Dir
Wend


If flag = 0 Then


MsgBox "Did not find " & fname & " model in folder. Please check all models are located in the correct folder and restart the Application"


closemodel




ElseIf flag = 1 Then


Workbooks.Open (fpath & file2)

Set wb2 = ActiveWorkbook






wb2.Application.Sheets(1).Cells(Cells.Rows.Count, Cells.Columns.Count).Value = owb.Name
wb2.Application.Sheets(1).Cells(Cells.Rows.Count - 1, Cells.Columns.Count).Value = fname
wb2.Application.Sheets(1).Cells(Cells.Rows.Count - 2, Cells.Columns.Count).Value = i
wb2.Application.Sheets(1).Cells(Cells.Rows.Count - 3, Cells.Columns.Count).Value = wb2.Name




ActiveWorkbook.Windows(1).Visible = False

owb.Activate

Sheets("MAP").Select

Cells(46 + i, 5).Value = fname & " Input"
Cells(46 + i, 6).Value = fname & " Output"
Cells(46 + i, 8).Value = wb2.Name

nameworksheet (i)


End If


Next i


compmessage 'another function


Hop:
Exit Sub




Else
'MsgBox "You Pushed No"
Exit Sub
End If


End Sub

 

[c2shar]

I am beginner in Excel VBA. Please do let me know if some more information is needed. Also, please read as 6 excel workbooks instead of 6 excel sheets.

Regards
Tushar

 

[c2shar]

Please help with this code for runtime error 1004. Not getting what is required to be done to debug this:

More info:

1. Using MS Excel 2010.
2. Error occurs at first line below after variables declaration.
3. There are 6 linked (for various calculations) workbooks to the main workbook.
4. When i open main file having this code, it automatically opens the constituent workbooks. Constituent workbooks are hidden after they are opened.
5. Error occurs ONLY IF i save hidden workbooks while closing all the files. Error message: Run-time Error '1004': Method 'Cells' of Object '_Global' failed

Thanks in advance.

Dim owb, wb2 As Workbook
Dim file, file2 As Variant, flag, index As Integer
Set owb = ActiveWorkbook




owb.Application.Sheets(1).Cells(Cells.Rows.Count, Cells.Columns.Count).Value = " "
owb.Application.Sheets(1).Cells(Cells.Rows.Count - 1, Cells.Columns.Count).Value = " "
owb.Application.Sheets(1).Cells(Cells.Rows.Count - 2, Cells.Columns.Count).Value = " "




ActiveWorkbook.Application.Sheets(1).Cells(Cells.Rows.Count, Cells.Columns.Count).Value = owb.Name
ActiveWorkbook.Application.Sheets(1).Cells(Cells.Rows.Count - 1, Cells.Columns.Count).Value = owb.Name


Sheets("MAP").Select


fpath = Left(ActiveWorkbook.FullName, InStr(ActiveWorkbook.FullName, ActiveWorkbook.Name) - 1)


For i = 1 To Range("C52").Value


    flag = 0
    
    fname = Cells(46 + i, 4)
    
    file = Dir(fpath)
   
   While (file <> "")
      If InStr(LCase(file), LCase(fname)) > 0 Then
         
         file2 = file
         flag = 1
         
      End If
     file = Dir
   Wend


   If flag = 0 Then


      MsgBox "Did not find " & fname & " model in folder. Please check all models are located in the correct folder and restart the Application"


      closemodel




   ElseIf flag = 1 Then


    Workbooks.Open (fpath & file2)
    
    Set wb2 = ActiveWorkbook






    wb2.Application.Sheets(1).Cells(Cells.Rows.Count, Cells.Columns.Count).Value = owb.Name
    wb2.Application.Sheets(1).Cells(Cells.Rows.Count - 1, Cells.Columns.Count).Value = fname
    wb2.Application.Sheets(1).Cells(Cells.Rows.Count - 2, Cells.Columns.Count).Value = i
    wb2.Application.Sheets(1).Cells(Cells.Rows.Count - 3, Cells.Columns.Count).Value = wb2.Name


   
    
    ActiveWorkbook.Windows(1).Visible = False
    
    owb.Activate
    
    Sheets("MAP").Select
    
    Cells(46 + i, 5).Value = fname & " Input"
    Cells(46 + i, 6).Value = fname & " Output"
    Cells(46 + i, 8).Value = wb2.Name
    
    nameworksheet (i)


   End If


Next i