Excel macro does not work when used as an addin
I have written a VBA macro that copies/merges multiple workbooks into one and it works as desired. But when I try to create this macro as an addin, and run the code by using my shortcut key of Ctrl+Shift+M, the code fails with no error. The code is as below:
Dim wbk As Workbook Dim wbk1 As Workbook Set wbk1 = Workbooks.Add Set wbk1 = ThisWorkbook Dim Filename As String Dim Path As String Dim Rowvar As Integer Rowvar = 1 Titlelabcnt = 1 Path = "C:\" Filename = Dir(Path & "*.xlsx") Do While Len(Filename) > 0 Set wbk = Workbooks.Open(Path & Filename) wbk.Activate Dim Lastcol As Long Dim LastRow As Long Lastcol = ActiveSheet.Cells.Find(What:="*", After:="A2", SearchOrder:=xlByColumns, searchdirection:=xlPrevious).Column LastRow = ActiveSheet.Cells.Find(What:="*", After:="A2", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row Range("A2").Select Range("A2").Resize(LastRow, Lastcol).Select Selection.Copy Destination:=wbk1.Sheets("sheet1").Range("A" & Rowvar) Rowvar = Rowvar + LastRow wbk.Close True Filename = Dir Loop End Sub
The above code stops when used as an addin right at the following line:
Set wbk = Workbooks.open(Path & Filename)
Can you provide any suggestions on what to change such that it works like an addin as well?