Error: 1004: Application-defined or object-defined error

[Hannah45432]

Hi, I am very new to writing VBA macro so apologies if my problem has an obvious solution. I have written a Macro to copy and paste the second sheet in multiple different workbooks in one folder to a new workbook so I can have it all in the one. However, my code isn't working and when I run the code, I get the above Error and it only pastes from the first workbook and leaves the first workbook open. I've pasted my VBA code below. Hopefully someone can help me fix it.

Thanks!

Sub Copy_Data_From_Multiple_Workbooks()

Dim FolderName As String
Dim FileName As String

FolderName = "C:\Users\......\Test Data\" (redacted for privacy)
FileName = Dir(FolderName)

'Loop start

Do While FileName <> ""
Workbooks.Open FolderName & FileName
ActiveWorkbook.Worksheets(2).Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = Replace(FileName, ".xlsx", "")
Workbooks(FileName).Close

FileName = Dir()

Loop

End Sub

 

[Joe4]

Welcome to the Board!

I believe that both FileName and FolderName are reserved words, as they already mean something to VBA.
You should NEVER use reserved words as the name of your Variables, Procedures, or Functions.

Try changing the names of your variables to something like "MyFolderName" and "MyFileName", and see if that makes any difference.

 

[Hannah45432]

Welcome to the Board!

I believe that both FileName and FolderName are reserved words, as they already mean something to VBA.
You should NEVER use reserved words as the name of your Variables, Procedures, or Functions.

Try changing the names of your variables to something like "MyFolderName" and "MyFileName", and see if that makes any difference.

Hi Joe,

I tried your suggestion but I'm still getting the same error message unfortunately.

 

[Joe4]

If you hit the "Debug" button, which line of code does it highlight?
Also, are you sure that the sheets you are trying to save don't have the same name?
What is the name of the file it creates?
What should be the name of the second?

 

[dmt32]

Hi,

Only lightly tested but see if this update to the code resolves your issue

Sub Copy_Data_From_Multiple_Workbooks()
    Dim wbSource    As Workbook, wbDest     As Workbook
    Dim FileName    As String, SheetName    As String
   
    '------------------------------------------------------------------------------------------------
    '                                            SETTINGS
    '------------------------------------------------------------------------------------------------
    Const FolderName As String = "C:\Users\......\Test Data\"        '(redacted for privacy)
    Const FileExt   As String = ".xlsx"
   
    '------------------------------------------------------------------------------------------------
   
    On Error GoTo myerror
    Application.ScreenUpdating = False
   
    'destination workbook
    Set wbDest = ThisWorkbook
   
    FileName = Dir(FolderName & "*" & FileExt, vbDirectory)
   
    'Loop start
    Do While FileName <> ""
        'get sheet name
        SheetName = Replace(FileName, FileExt, "")
        'check if sheet name exists
        If Not Evaluate("ISREF('" & SheetName & "'!A1)") Then
            'open workbook read only
            Set wbSource = Workbooks.Open(FolderName & FileName, 0, True)
            'copy second worksheet from source workbook
            wbSource.Worksheets(2).Copy after:=wbDest.Worksheets(wbDest.Worksheets.Count)
            'rename sheet
            ActiveSheet.Name = SheetName
            'close source workbook without saving
            wbSource.Close False
        End If
       
nextfile:
        FileName = Dir()
        'clear object variable
        Set wbSource = Nothing
    Loop
   
myerror:
    If Not wbSource Is Nothing Then wbSource.Close False
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    'if no sheet 2 then continue
    If Err.Number = 9 Then Resume nextfile
End Sub

Note: whilst fully agree that you should avoid using any names that are the same as the function, statement, method, and intrinsic constant names used in Visual Basic - Your declared variables though, are generally used as parameter names in the language which are not shown on Reserved list & should not cause an issue if want to use them.

However and for future reference, you can avoid any potential conflicts in a VBA project by following a naming convention known as Hungarian notation where your variable name includes a prefix that indicates its Data Type.

For example,
"str" - for string variables,
"int" - for integer variables,
"bln" - for Boolean values,
"dbl" - for doubles,

But there is no hard and fast rule that you have to do this. If you do though, try to use a reserved name, you will find the compiler unlikely to let you

Example

Sub Date()

End Sub

Finally, If update does not resolve the issue, then to give forum better understanding of your project, are you able to place copy of your workbook (with sensitive data removed) in a file sharing site like DropBox & provide link to it here. Plenty here to assist you.

Hope Helpful

Dave