Results 1 to 10 of 11

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker dancko's Avatar
    Join Date
    Jul 2014
    Location
    Italy
    Posts
    6
    Articles
    0

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

    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)  -- At this line I get the error.
        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 by dancko; 2014-07-31 at 10:30 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •