0

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

Improve this question
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 Commented Feb 16, 2018 at 14:19

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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".
The nature of both problems is the same. You have Rows.Count again which is not qualified to a particular worksheet.
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.<property>, 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.
Thank you so much, I figured it out. it works perfectly now.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.