Excel VBA method Cells of object _global failed

  • Thread starter Thread starter spoefi
  • Start date Start date
S

spoefi

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
appExcel1.Workbooks.Open("c:\projects\macros\template.xls").Sheets(1)
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
 
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
 
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

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
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.

Code: 
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
 
Back
Top