Run Macro in Excel AFTER opening a Workbook

Question

I want to run a Macro right after the Workbook opened as for some reasons the Macro requires the workbook to be opened before. Workbook_activate is not an option as the macro opens other files and therefore would result in an infinite loop.

The macro is working perfectly fine if i run it from the VBA-Editor, but it always throws

Runtime Error 1004: The method Cells for the Object _Global has failed

when i actually open the workbook.

This is the code im using:

Private Sub Workbook_Open()
  Call updateColumn("sheet", "Tabelle2", 1, 2)
  Call updateColumn("sheet", "Tabelle2", 5, 1)    
  Call updateSG("Tabelle2", "sheet")
End Sub

updateColumn updates the given column by opening another excel file and synchronizing the data from there. It works fine so i won't go into it here.

The Problem is always in the updateSG sub. It does basically the same as updateColumn, but it also iterates over the other file and sorts the required entries into corresponding columns.

Private Sub updateSG(sheetname As String, adbSheet As String)
Dim adb As Workbook
Dim report As Workbook
Dim row As Range
Dim fak As String
Dim N As Long
Dim rownumber As Long
Set report = Workbooks("thisfile.xlsm")
Set adb = Workbooks.Open(report.Path & "/ADB.xls")
rownumber = Cells(adb.Sheets(adbSheet).Rows.Count, 1).End(xlUp).row
For i = 2 To rownumber
    fak = adb.Sheets(adbSheet).Cells(i, 1).Value
    Select Case fak
        Case "E"
            N = report.Sheets(sheetname).Cells(Rows.Count, "C").End(xlUp).row + 1
            report.Sheets(sheetname).Cells(N, "C").Value = adb.Sheets(adbSheet).Cells(i, 3)
        Case "G"
            N = report.Sheets(sheetname).Cells(Rows.Count, "D").End(xlUp).row + 1
            report.Sheets(sheetname).Cells(N, "D").Value = adb.Sheets(adbSheet).Cells(i, 3)
        Case "I"
            N = report.Sheets(sheetname).Cells(Rows.Count, "E").End(xlUp).row + 1
            report.Sheets(sheetname).Cells(N, "E").Value = adb.Sheets(adbSheet).Cells(i, 3)
        Case "M"
            N = report.Sheets(sheetname).Cells(Rows.Count, "F").End(xlUp).row + 1
            report.Sheets(sheetname).Cells(N, "F").Value = adb.Sheets(adbSheet).Cells(i, 3)
        Case "P"
           N = report.Sheets(sheetname).Cells(Rows.Count, "G").End(xlUp).row + 1
            report.Sheets(sheetname).Cells(N, "G").Value = adb.Sheets(adbSheet).Cells(i, 3)
        Case "T"
            N = report.Sheets(sheetname).Cells(Rows.Count, "H").End(xlUp).row + 1
            report.Sheets(sheetname).Cells(N, "H").Value = adb.Sheets(adbSheet).Cells(i, 3)
    End Select
Next
For i = 3 To 8
    report.Sheets(sheetname).Columns(i).RemoveDuplicates Columns:=Array(1)
Next
Workbooks("ADB.xls").Close SaveChanges:=False

End Sub

The issue is in the Line

rownumber = Cells(adb.Sheets(adbSheet).Rows.Count, 1).End(xlUp).row

and the lines from the select case going as

N = report.Sheets(sheetname).Cells(Rows.Count, "C").End(xlUp).row + 1

These lines basically count the elements in a Column. Again, it works perfectly if i run it while the Workbook is open, but always throws the error when i actually open the file.

I hope someone here can help me.

Kind regards

Michael

Cells is not qualified to a specific workbook or worksheet. As such, it falls back to the global Cells which defaults to using the Cells object of whatever worksheet is Active. Failing to scope your objects properly is like the #1 cause of 1004 errors. See this — David Zemens

David Zemens David Zemens · Accepted Answer · 2018-02-16T14:19:08

Cells is not qualified to a specific workbook or worksheet. As such, it falls back to the global Cells which defaults to using the Cells object of whatever worksheet is Active. Failing to scope your objects properly is like the #1 cause of 1004 errors. See this

Change this:

Set adb = Workbooks.Open(report.Path & "/ADB.xls")
rownumber = Cells(adb.Sheets(adbSheet).Rows.Count, 1).End(xlUp).row

Because in the above Cells statement, Cells refers to ActiveSheet (by default), which may or may not be the adbSheet name. If that sheet isn't active, error is expected.

To this, in order to ensure you've qualified Cells to the adb workbook object and adbSheet worksheet.

Set adb = Workbooks.Open(report.Path & "/ADB.xls")
With adb.Sheets(adbSheet)
    rowNumber = .Cells(.Rows.Count,1).End(xlUp).Row
End With

Run Macro in Excel AFTER opening a Workbook

1 Answers