PDA

View Full Version : VBA Code error from Macro Add-In



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

Bob Phillips
06-13-2017, 03:42 PM
It doesn't make it an addin just because you run it from the ribbon, you have to save it as an xlam file to be an addin.

If you have done that, have you activaed any workbook, the addin itself will be ThisWorkbook, not the active workbook.

snb
06-14-2017, 12:30 AM
Sub GetFiles()
c00 = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Banks\"
c01 = Dir(c00 & "*.xls")
c02 =activeworkbook.name

Do While c01 <> ""
with getobject(c00 & c01)
For Each it In .Sheets
it.Copy , Workbooks(c02).Sheets(workbooks(c02).sheets.count)
Next
.Close 0
end with
c01 = Dir
Loop
end sub

Tira
06-15-2017, 07:47 AM
Yes, I did that before I added it to the ribbon. I tried "ThisWorkbook" as well and it did not work. I just can't understand how a perfectly operating macro keeps getting hung up when I try to run it through the ribbon.

Tira
06-15-2017, 07:51 AM
This code worked!! Thank you VBAX Guru. I just added to it so that it opens to sets of files and then save as.