Run-time error '1004': Method 'Sheets' of object '_Global' failed

D

dancko

New member
Joined
Jul 31, 2014
Messages
6
Reaction score
0
Points
0
Location
Italy
Hi to all, I'm a new entry in this forums and a newbie in excel programming.

If is not a trouble for you I would like ask to you about the error in the Title post: "Run-time error '1004': Method 'Sheets' of object '_Global' failed".

More precisely my problem is this:

Every Day they send me an email with a xls file attachment. I download this attachment xls file on my pc, open it and I do some operations with the data stored in the file. The operations are always the same.
For speed up this operations, I created an automatic macro with the "Record a Macro" feature in view page and I stored (saved) it in Personal (global) folder so that I can use the Macro in all xls file which send me. So far, that's all right, when I open the attachment file run a macro MANUALLY and all work fine.

The problem arise when I want run a macro AUTOMATICALLY when I open the xls file, that is, the macro has to start up automatically as soon as the file opens.

To realize that I renamed the macro (saved as module in the Personal (global) folder) in Auto_Open().

Now, when I open the xls file I get this error: "Run-time error '1004': Method 'Sheets' of object '_Global' failed".

Can anyone of you help me?

Thanks in advance.

The code of Macro is this:

Code: 
Sub Auto_Open()

'
' Auto_Open Macro
'
'
    Sheets.Add After:=Sheets(Sheets.Count)  [B]-- At this line I get the error.[/B]
    Sheets("Foglio1").Select
    Sheets("Foglio1").Name = "all"
    Sheets("all").Select
    Sheets("all").Move Before:=Sheets(1)
    Sheets("sheet1").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A7").Select
    Sheets("all").Select
    ActiveSheet.Paste
    Range("A7").Select
    Sheets("sheet2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    Sheets("all").Select
    ActiveSheet.Paste
    Range("A13").Select
    Sheets("sheet3").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    Sheets("all").Select
    ActiveSheet.Paste
    Range("A19").Select
    Columns("A:A").ColumnWidth = 16.86
    Columns("B:B").ColumnWidth = 19.29
End Sub

Explain of code: I create a new Sheet, rename it in "all", then I copy the content of sheet1, sheet2 and sheet3 in "all".
 
Last edited:
Assuming that the workbook with the code remains hidden as it is in the personal folder, this should do it

Code: 
Sub Auto_Open()

'
' Auto_Open Macro
'
'
    With ActiveWorkbook
    
        .Worksheets.Add After:=.Worksheets(.Worksheets.Count)  '-- At this line I get the error.
        .Worksheets("Foglio1").Name = "all"
        .Worksheets("all").Move Before:=.Worksheets(1)
        .Worksheets("sheet1").Range("A1").CurrentRegion.Copy .Worksheets("all").Range("A1")
        .Worksheets("sheet2").Range("A1").CurrentRegion.Copy .Worksheets("all").Range("A1").End(xlDown).Offset(1, 0)
        .Worksheets("sheet3").Range("A1").CurrentRegion.Copy .Worksheets("all").Range("A1").End(xlDown).Offset(1, 0)
        .Columns("A:A").ColumnWidth = 16.86
        .Columns("B:B").ColumnWidth = 19.29
    End With
End Sub
 
Hi Bob, thanks for the respose.

Yes, the workbook with the code remains hidden as it is in the personal folder.

So, I copy and paste your code but now I get this error when open a file:

"run-time error 91 object variable or with block variable not set"
 
You don't say where, so I will guess

Code: 
Sub Auto_Open()

    With ActiveWorkbook
    
        .Worksheets.Add After:=.Worksheets(.Worksheets.Count)  '-- At this line I get the error.
        ActiveSheet.Name = "all"
        .Worksheets("all").Move Before:=.Worksheets(1)
        .Worksheets("sheet1").Range("A1").CurrentRegion.Copy .Worksheets("all").Range("A1")
        .Worksheets("sheet2").Range("A1").CurrentRegion.Copy .Worksheets("all").Range("A1").End(xlDown).Offset(1, 0)
        .Worksheets("sheet3").Range("A1").CurrentRegion.Copy .Worksheets("all").Range("A1").End(xlDown).Offset(1, 0)
        .Columns("A:A").ColumnWidth = 16.86
        .Columns("B:B").ColumnWidth = 19.29
    End With
End Sub
 
Sorry Bob, but I get always the same error: "run-time error 91 object variable or with block variable not set"
 
On which line?
 
dancko said:
At the first line: .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
Click to expand...

For precision, I think that the error is at first line but I do not sure.
Please can you tell me how can I see in which line is the error?
When I open the xls file a popup tells me that there is this error: "run-time error 91 object variable or with block variable not set"
 
Last edited:
It should be highlighting the line in error.

Can you post the two workbooks, should crack it much quicker that way.
 
Yes, workbooks are files. Go into The Advanced option when replying, and then the Manage Files button.
 
You must log in or register to reply here.
Back
Top