Excel VBA method Cells of object _global failed



run time error 1004
Method 'Cells' of object '_global' failed

I try to copy a range from one sheet to another in another workbook.
I get an error on the part where I define my source range (marked wit
'XXXX') Why?

Important maybe: the code is in Outlook

Dim Sheettempl As Worksheet
Set Sheettempl
Dim Sheetbasis As Worksheet
Set Sheetbasis = appExcel1.Workbooks.Open("c:\projects\macros\test"
intTeller & ".xls").Sheets(1)

Dim srceRange As Range
Dim destRange As Range

Set srceRange = Sheetbasis.Range(Cells(2, 2), Cells(8, 2)) 'XXXX
Set destRange = Sheettempl.Cells(2, 2)

srceRange.Copy destRang



Jon Peltier

Two questions:

1) Is Sheets(1) not a worksheet?

2) Does this work:

With Sheetbasis
Set srceRange = .Range(.Cells(2, 2), .Cells(8, 2))
End With

- Jon

Tushar Mehta

Set srceRange = Sheetbasis.Range(Cells(2, 2), Cells(8, 2)) 'XXXX

The line above contains an unqualified reference to the Cells property.
If you look up XL VBA help, you will find that XL treats such an
unqualified reference as applying to the ActiveSheet. If the active
sheet is not a worksheet or if it is not the same as Sheetbasis, you
will get an error.

One way to do this would be:

with Sheetbasis
Set srceRange = .Range(.Cells(2, 2), .Cells(8, 2))
end with


Tushar Mehta
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


Jun 15, 2015
Reaction score
I have same error in my code: Can anyone please help with this:

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 and also at block of statements wb2.Application.Sheets(1).Cells......etc.
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


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

   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"


   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
    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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question