Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

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

  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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    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

  3. #3
    Seeker dancko's Avatar
    Join Date
    Jul 2014
    Location
    Italy
    Posts
    6
    Articles
    0
    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"

  4. #4
    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

  5. #5
    Seeker dancko's Avatar
    Join Date
    Jul 2014
    Location
    Italy
    Posts
    6
    Articles
    0
    Sorry Bob, but I get always the same error: "run-time error 91 object variable or with block variable not set"

  6. #6
    On which line?

  7. #7
    Seeker dancko's Avatar
    Join Date
    Jul 2014
    Location
    Italy
    Posts
    6
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    On which line?
    At the first line: .Worksheets.Add After:=.Worksheets(.Worksheets.Count)

  8. #8
    Seeker dancko's Avatar
    Join Date
    Jul 2014
    Location
    Italy
    Posts
    6
    Articles
    0
    Quote Originally Posted by dancko View Post
    At the first line: .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
    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 by dancko; 2014-08-01 at 09:08 AM.

  9. #9
    It should be highlighting the line in error.

    Can you post the two workbooks, should crack it much quicker that way.

  10. #10
    Seeker dancko's Avatar
    Join Date
    Jul 2014
    Location
    Italy
    Posts
    6
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    It should be highlighting the line in error.
    If so, then the error is at the first line

    Quote Originally Posted by Bob Phillips View Post
    Can you post the two workbooks, should crack it much quicker that way.
    Sorry, how can I post the two workbook? Do you say the two excel file?

Page 1 of 2 1 2 LastLast

Posting Permissions

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