VBA Code Works in

[CaliInAZ]

Hello All!

I'm attempting to copy several workbooks in a folder into one workbook.

The code works when the spreadsheet is saved as a .xlsm. However, when I save the exact same code as Excel Add-ins, I continually get the following error:

Run-time error '1004'": Copy method of Worksheet class failed

Here is the code I'm using:

Sub MergeWorkbooks()
Dim FolderPath As String
Dim File As String
FolderPath = "path location"
File = Dir(FolderPath)
Do While File <> ""
Workbooks.Open FolderPath & File
ActiveWorkbook.Worksheets(1).Copy _
after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = Replace(File, ".xlsx", "")
Workbooks(File).Close
File = Dir()
Loop
MsgBox "All workbooks have been added! Please rename all tables."
End Sub

The code works until it has to copy the second workbook in the folder.

The error happens here:
ActiveWorkbook.Worksheets(1).Copy _
after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

I have no idea why this isn't working as an Excel Add-in.

Any help would be greatly appreciated. Thank you!

 

[HaHoBe]

Hi CalilinAZ,

a macro enabled workbook is visible in the normal view window while any addin isn´t. And for me an addin is to hold data and codes but not to be used for sampling data.

Maybe alter the approach to work with a new workbook like

Sub MergeWorkbooks_01_210730()
'https://www.mrexcel.com/board/threads/vba-code-works-in.1177846/

Dim FolderPath As String
Dim File As String
Dim wbkNew As Workbook
Dim wbkOpen As Workbook

FolderPath = "E:\Temp\"
File = Dir(FolderPath & "*.xlsx")

Do While File <> ""

  Set wbkOpen = Workbooks.Open(FolderPath & File)
  If wbkNew Is Nothing Then
    wbkOpen.Worksheets(1).Copy
    Set wbkNew = ActiveWorkbook
  Else
    wbkOpen.Worksheets(1).Copy after:=wbkNew.Worksheets(wbkNew.Worksheets.Count)
  End If
  wbkNew.ActiveSheet.Name = Replace(File, ".xlsx", "")
  wbkOpen.Close
  File = Dir()
Loop

MsgBox "All workbooks have been added! Please rename all tables."

End Sub

Ciao,
Holger

 

[CaliInAZ]

Hello HaHoBe!

Thank you so much for responding and thank you even more because your approach worked.

VBA is fairly new to me and I'm still learning. I sincerely appreciate you taking the time to set me straight.

Your New Grateful Friend,
CaliInAZ