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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — David Zemens, Feb 16 '18 at 14:19

score 1 · Accepted Answer · edited Feb 16 '18 at 14:51

1

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

edited Feb 16 '18 at 14:51

braX

11,506
5
20
33

answered Feb 16 '18 at 14:19

David Zemens

53,033
11
81
130

Thanks for your reply. The rownumber now works, but the error now pops up in thte `N = report.Sheets(sheetname).Cells(Rows.Count, "E").End(xlUp).row + 1` and I'm not entirely sure how to fix it here as it is supposed to only count the entries in column "E". – Michael Villani Feb 16 '18 at 14:28
The nature of both problems is the same. You have `Rows.Count` again which is not qualified to a particular worksheet. – David Zemens Feb 16 '18 at 14:29
`Cells`, `Rows`, `Columns`, `Range`, etc., these are all child-objects/properties of a `Worksheet`, and if you're not explicitly assigning them against a specific worksheet, Excel will always interpret them impicitly as `ActiveSheet.`, which is prone to failure for a number of reasons, in this case you're trying to define a range that spans multiple sheets -- which is not allowed. – David Zemens Feb 16 '18 at 14:32
Thank you so much, I figured it out. it works perfectly now. – Michael Villani Feb 16 '18 at 14:34

Run Macro in Excel AFTER opening a Workbook

1 Answers1