Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Code throwing 1004 Error
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,126
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    That looks like a temp file, is the code located in SCM Enterprises.xlsm?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    Well I got, finally, that there was a hidden file. I removed that and now get a new error.

    Run-time error '9'
    subscript out of range

    I don't know what this one means.

    Terry E

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,126
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    It means that the sheet you are referencing does exist in that workbook
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #14
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    Now I'm more confused. None of the programming calls sheets by name. It loops through all workbooks in the folder.

    Also, am I missing something on getting the VB popup box to give me the debug option? I'm on Windows 10 and I'm using Subscription Office 365.

    Terry E

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,126
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    Which code are you talking about? The code in your OP, or the code in post#10?

    Why you are not getting the standard "Debug" window, I have no idea, unless MS have changed it (which I doubt)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    The op (below). None of the programming calls anything by name except the sheet name "Statement" and all of my workbooks have been created from a template so they are ALL identical to each other. Like I said, up until last week this macro worked.

    Code:
    Sub DataExtract()
    
    With Application
     .DisplayAlerts = False
     .ScreenUpdating = False
    End With
    
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile  As Object
    Dim wb As Workbook
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\Statements\")
    
    For Each objFile In objFolder.Files
        If InStr(objFile, ".xls") Then
            Workbooks.Open (objFile)
        End If
        Set wb = ActiveWorkbook
        i = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row + 1
        j = wb.Sheets("Statement").Cells(Rows.Count, "C").End(xlUp).Row
        If wb.Sheets("Statement").Range("C13") <> vbNullString Then
            wb.Sheets("Statement").Range("A13:F" & j).Copy
            Sheet1.Range("B" & i).PasteSpecial xlPasteValuesAndNumberFormats
            wb.Sheets("Statement").Range("I13:I" & j).Copy
            Sheet1.Range("H" & i).PasteSpecial xlPasteValuesAndNumberFormats
            k = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
            wb.Sheets("Statement").Range("F6").Copy
            Sheet1.Range("A" & i & ":A" & k).PasteSpecial
            Application.CutCopyMode = False
        End If
        wb.Close
    Next
    
    Sheet1.Range("A1") = "All Invoices: " & Format(Date, "mmmm d, yyyy") & ", Week " & Format(Date, "ww")
    'Align & Format Date text cell
    Range("A1").RowHeight = 30
    Range("A1").Font.Name = "Arial"
    Range("A1").Font.Size = 16
    Range("A1").IndentLevel = 1
    Range("A1").VerticalAlignment = xlCenter
    Range("A1").HorizontalAlignment = xlGeneral
    
    MsgBox "Task Complete!"
    
    With Application
     .EnableEvents = True
     .ScreenUpdating = True
     .Calculation = xlCalculationAutomatic
    End With
    
    End Sub
    Terry E

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,126
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    Best guess is that either one of the workbooks does not have a sheet called "Statement" (may have been changed or deleted), or the workbook containing the code does not have a sheet with the codename "Sheet1"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #18
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    Eureka!

    First off, thanks for all the help. The problem was another VERY hidden file. Unchecking the "show hidden..." did not show this file, it was "desktop.ini". Once I found it, and I only found it with the 2nd set of code I posted that had the ~?CMS file displayed. I missed this one in the list because it simply showed up in the "Ds" instead of at the bottom of the list like the ~?CMS file did.

    The code is working now and I want to say a big thank you for taking the time to help.

    Terry E

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,126
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    Glad you sorted it & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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