Tira
06-13-2017, 11:19 AM
I created a Macro and tried to add it to the Excel Ribbon as a new tab and the Macro will not run properly. It keeps getting hung up at "Sheet.Copy After:=ActiveWorkbook.Sheets(1)". The Macro is to open up a bunch of files into one workbook. Below is the complete VBA code. The Macro works fine if I run it normally but not as an Add-In from the Excel Ribbon.
Sub GetFiles()
Path = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Banks\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Path = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\MACs\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
ChDir "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File"
ActiveWorkbook.SaveAs Filename:= _
"G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Volume Summary Report.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Sub GetFiles()
Path = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Banks\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Path = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\MACs\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
ChDir "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File"
ActiveWorkbook.SaveAs Filename:= _
"G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Volume Summary Report.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub